LARGE Function with multiple criteria

[ExcelHelpPls]

Greetings,

I'm hoping someone can help me with this problem that I'm guessing there is an easy answer too (but i can't figure it out).

I'm trying to find the nth largest value of a column "C" based on criteria from both column "A" and "B". For example:

A B C
Yes 1 500
Yes 5 600
No 1 800
Yes 2 900

The logic I need to folllow is "If column A = "Yes" and column B = "1 or 2 or 3" return the nth largest value in column C of all values that meet those two requirements.

I was able to use the LARGE function with an IF statement to return the correct value of one criteria (LARGE(IF(A1:A5="Yes",C1:C5),1), but can't figure out how to add the second criteria.

Any help would be greatly appreciated.

Thank you

 

[balajayan]

Hi mtyrrell13,

balajayan - I am trying a variation of your suggested code and coming up with an error

'{=LARGE(IF((E21:E1000="ACTIVE")*((S2:S1000="ORG A")+(J2:J1000=,">="&Z2)),M2:M1000),1)}

What I am trying to do is build an array of "ACTIVE" records only, that are owned by "ORG A", that have an end date on or before 12/31/13 (the value in Cell Z2), the return the Largest value in Column M that meet the above criteria

There seems to be small mistake. The range E21:E1000 = "ACTIVE" is not equal to other ranges in the formula. It should be E2:E1000="ACTIVE". Please change this and confirm whether it is working.

 

[Andrew Poulsom]

Also:

J2:J1000=,">="&Z2

should be:

J2:J1000>=Z2

 

[systematical88]

Hi,

Try the below:

LARGE(IF((A1:A7="YES")*((B1:B7="Tier 1")+(B1:B7="Tier 2")),C1:C7),2)

The above is array formula. Please press Control+Shift+Enter instead just Enter after keying in the formula.
Modify the range and constant values as per your requirement.

balajayan, I was looking for a large if with 2 criteria and this worked for me, thank you much!

 

[Peter_SSs]

balajayan, I was looking for a large if with 2 criteria and this worked for me, thank you much!

Excel now also has the AGGREGATE function that can enable this sort of calculation without the Ctrl+Shift+Enter requirement.

For example, below looking for the 2nd largest value in column C where Col A is "Yes" and col B is from 1 to 3.

Excel Workbook
	A	B	C	D	E	F
1	Yes	1	450		Col A	Yes
2	Yes	5	600		Col B min	1
3	No	1	800		Col B Max	3
4	Yes	2	900		n	2
5	Yes	3	200		Result	500
6	No	4	300
7	Yes	0	800
8	Yes	2	500
nth largest

 

[systematical88]

Excel now also has the AGGREGATE function that can enable this sort of calculation without the Ctrl+Shift+Enter requirement.

For example, below looking for the 2nd largest value in column C where Col A is "Yes" and col B is from 1 to 3.

Excel Workbook
	A	B	C	D	E	F
1	Yes	1	450		Col A	Yes
2	Yes	5	600		Col B min	1
3	No	1	800		Col B Max	3
4	Yes	2	900		n	2
5	Yes	3	200		Result	500
6	No	4	300
7	Yes	0	800
8	Yes	2	500
nth largest

I have used that function in Power BI, but was not aware that it was also in Excel now, thank you!

 

[Peter_SSs]

I have used that function in Power BI, but was not aware that it was also in Excel now, thank you!

Cheers.