excel summing N largest values by condition

  • Thread starter Thread starter stuart
  • Start date Start date
S

stuart

Hello there,

I'm struggling with an array formula that will return the SUM of the
Top 20 values in a list that meet a given criteria.

E.g. SUM the Top 20 instances of Dog.


Condition Field Data Field
Dog 5000
Dog 4954
Cat 4221
Dog 3465
Etc.


I can happily return the overall Top 20 using the following array
formula:

{=SUM(LARGE(Data,ROW(INDIRECT("1:20"))))}

However i'm stumped on how to add a condition to the formula.


Hope someone can help...
 
Hello there,

I'm struggling with an array formula that will return the SUM of the
Top 20 values in a list that meet a given criteria.

E.g. SUM the Top 20 instances of Dog.


Condition Field Data Field
Dog 5000
Dog 4954
Cat 4221
Dog 3465
Etc.


I can happily return the overall Top 20 using the following array
formula:

{=SUM(LARGE(Data,ROW(INDIRECT("1:20"))))}

However i'm stumped on how to add a condition to the formula.


Hope someone can help...
Click to expand...

Try something like
=SUM(LARGE((A1:A100="dog")*(B1:B100),ROW(1:20)))
array-entered.
 
=SUM(LARGE(IF(A2:A37="Dog",B2:B37),ROW(INDIRECT("1:"&MIN(20,COUNTIF(A:A,"Dog"))))))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Hi Stuart:

If you already have a good method to get the top 20 from a column, then in C1:

=IF(A1="dog",B1,-9999) and copy down. Then get the top 20 from column C
 
Thanks Stephen,

This works a treat...

{=SUM(LARGE((A1:A100="Dog")*(B1:B100),ROW(INDIRECT("1:20"))))}

Unfortunately stumps me an the next permutation which is:

SUM the VALUE data
WHERE condition = Dog
AND
VOLUME is in Top 20


Condition Field Volume Field Value Field
Dog 5000 99.99
Dog 4954 126.98
Cat 4221 44.76


Don't suppose you've any ideas?
 
Try the following array formula:

=SUM(IF((A1:A100="A")*(RANK(B1:B100,B1:B100)<5),C1:C100))

Does this work?
Kostis Vezerides
 
Back
Top