Conditional Large

[Invoice]

Hi,

Is there a way to calculate the k largest number of only those numbers
in column A which have a certain criteria in column B.

For example,

A B
1 a
2 a
3 a
4 b

I want the function to look at the whole list in column A and return
the k largest number of those that have "a" in column B, so the answer
here should be 3.

What about if there were more conditions in columns C, D, etc.?

Thanks

 

[vezerid]

Hi,

Is there a way to calculate the k largest number of only those numbers
in column A which have a certain criteria in column B.

For example,

A B
1 a
2 a
3 a
4 b

I want the function to look at the whole list in column A and return
the k largest number of those that have "a" in column B, so the answer
here should be 3.

What about if there were more conditions in columns C, D, etc.?

Thanks

You need an array formula for this (i.e. commit with Shift+Ctrl+Enter)

=SMALL(IF(A1:A3="a",B1:B3),k)

In general the structure of such a formula with multiple conditions
would be something like (always *array* entered):

=SMALL(IF((A1:A3>4)*(B1:B3="x")*(MOD(C1:C3,2)=0),D13),k)

or, abstractly:

=SMALL(IF((Cond1)*(Cond2)*...*(CondN), Data),k)

HTH
Kostis Vezerides

 

[Guest]

=SUMPRODUCT(MAX((B1:B4="a")*A1:A4))

more than one conditions
=SUMPRODUCT(MAX((B1:B100="a")*(C1:C100="x")*(D1100="y")*A1:A100))