Conditional Large
 

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

Conditional Large
 

On May 8, 11:49 am, Invoice wrote:
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

--
Invoice

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:A34)*(B1:B3="x")*(MOD(C1:C3,2)=0),D 1:D3),k)

or, abstractly:

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

HTH
Kostis Vezerides

Conditional Large
 

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

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


"Invoice" wrote:


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




--
Invoice