Conditional Large

  • Thread starter Thread starter Invoice
  • Start date Start date
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
 
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
 
=SUMPRODUCT(MAX((B1:B4="a")*A1:A4))

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