# Thread: Help on SUMIF calculation

1. ## Help on SUMIF calculation

Hi,

I'm having a problem on how to use the SUMIF calculation correct.

My problem is:
I want to show the top5 biggest deviations between two numbers.

This is quite easily and can be done by this formula:
=SUM(LARGE(Inputs!H:H;{1}))

What i then want to do, is to dinstinguish the deviations according to the actual week.

If i have a coloumn with weeks, a column with eg. input, one with output, and one with output.

How can i then use the SUMIF function to summarize the top 5 deviations in the actual week?

Can anybody help?

- Sigvaldaon  Reply With Quote

2. Is this what you want?

=SUMPRODUCT(LARGE(Inputs!H:H,ROW(INDIRECT("1:5"))))  Reply With Quote

3. I want to show the biggest deviation according to what the actual week is. If i for instance are having week 22, but the table contains data from week 1 to week 22, how can i then distinguish the top 5 deviations in week 22 from all the other weeks?  Reply With Quote

4. No idea without seeing how the data is layed out.  Reply With Quote

5. is there any way of sharing the excel template in here?  Reply With Quote

6. Click the 'Go ADvanced' button, and there is a 'Manage ATtachments' button there.  Reply With Quote

7. ## Example

Hi heres an example of my sheet.

In the sheet "Top 5 Deviations" it is showing the biggest deviations for all weeks. What i want is to only show the top 5 deviations from the actual week.  Reply With Quote

8. Use these array formulae:

A11: =SUM(LARGE(IF(LOOKUP_SHEET!B:B=\$B\$3,Inputs!E:E),ROW(A1)))

C11: =INDEX(LOOKUP_SHEET!C:C,MATCH(1,(\$A11=LOOKUP_SHEET!\$A1:\$A200)*(\$B11=LOOKUP_SHEET!\$B1:\$B200),0))  Reply With Quote

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•