Finding MAX value based on combination of two criteria

[JonHaywood]

Hi,
I want to show a maximum value of an array (of dates) based on a combination of two criteria (a kind of 'MAXIFS' function).
My data is a bit like this ...
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Date[/TD]
[TD="align: center"]Criteria 1 met?[/TD]
[TD="align: center"]Criteria 2 met?[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]01/03/2015[/TD]
[TD="align: center"]y[/TD]
[TD="align: center"]y[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]15/06/2015[/TD]
[TD="align: center"]n[/TD]
[TD="align: center"]y[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]21/06/2015[/TD]
[TD="align: center"]y[/TD]
[TD="align: center"]y[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]08/01/2016[/TD]
[TD="align: center"]n[/TD]
[TD="align: center"]y[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]12/02/2016[/TD]
[TD="align: center"]y[/TD]
[TD="align: center"]n[/TD]
[/TR]
</tbody>[/TABLE]

I'm trying to create a formula to show the last date that both criteria were both "y" (should be "21/06/2015" from the data above).

I can use this formula (entered as an array formula) to show the last date based on one matching criteria...

=MAX(IF(C2:C6="y",A2:A6,0))

(returns "08/01/2016")

...but when I use 'AND' to choose both criteria I only get the 'value if false' returned...

=MAX(IF(AND(B2:B6="y",C2:C6="y"),A2:A6,0))

(again, entered as an array formula, returns 0)

Is the AND function not compatible with array formulae? Am I missing something basic?

If I'm barking up the wrong tree, is there another way to do this?

Many thanks

Jon

 

[Canapone]

Hi,

you could try

=MAX(IF(B2:B6="y",IF(C2:C6="y",A2:A6,0)))

=MAX(IF(B2:B6&C2:C6="yy",A2:A6,0))

array entered

or

=MAX(INDEX((B2:B6="y")*(C2:C6="y")*A2:A6,))

just enter

 

[JonHaywood]

Canapone... you're my saviour!!

Thank you. They both work brilliantly!!

 

[Canapone]

Ciao

thanks for providing a kind feedback.

Greetings from Italy