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

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.

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

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

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
--
Gary''s Student - gsnu2007a


" wrote:

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

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 -

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 -