Try the following array formula:

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

Does this work?
Kostis Vezerides

On Nov 23, 3:23 pm, wrote:
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?

On 23 Nov, 12:13, "Stephen" <none wrote:



wrote in message

...

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.- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -