Conditional Large

I

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
 
V

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
Click to expand...

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),D1:D3),k)

or, abstractly:

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

HTH
Kostis Vezerides
 
T

Teethless mama

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

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