excel summing N largest values by condition

[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...

 

[Stephen]

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

Try something like
=SUM(LARGE((A1:A100="dog")*(B1:B100),ROW(1:20)))
array-entered.

 

[Bob Phillips]

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

 

[Gary''s Student]

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

 

[stuart]

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?

 

[vezerid]

Try the following array formula:

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

Does this work?
Kostis Vezerides