# 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

2. Is this what you want?

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

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?

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

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

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

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.

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))

#### Posting Permissions

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