PDA

View Full Version : Help on SUMIF calculation

Sigvaldason
2011-08-23, 11:49 AM
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
2011-08-23, 12:38 PM
Is this what you want?

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

Sigvaldason
2011-08-23, 03:19 PM
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
2011-08-23, 03:21 PM
No idea without seeing how the data is layed out.

Sigvaldason
2011-08-23, 03:23 PM
is there any way of sharing the excel template in here?

Bob Phillips
2011-08-23, 03:46 PM
Click the 'Go ADvanced' button, and there is a 'Manage ATtachments' button there.

Sigvaldason
2011-08-24, 11:43 AM
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.

Bob Phillips
2011-08-24, 01:02 PM
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))