Help on SUMIF calculation

Sigvaldason

New member
Joined
Aug 23, 2011
Messages
4
Reaction score
0
Points
0
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
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
Is this what you want?

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

Sigvaldason

New member
Joined
Aug 23, 2011
Messages
4
Reaction score
0
Points
0
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?
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
No idea without seeing how the data is layed out.
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
Click the 'Go ADvanced' button, and there is a 'Manage ATtachments' button there.
 

Sigvaldason

New member
Joined
Aug 23, 2011
Messages
4
Reaction score
0
Points
0
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.
 

Attachments

  • Example.xls
    40 KB · Views: 19

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
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))
 
Top