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 ...

	A	B	C
1	Date	Criteria 1 met?	Criteria 2 met?
2	01/03/2015	y	y
3	15/06/2015	n	y
4	21/06/2015	y	y
5	08/01/2016	n	y
6	12/02/2016	y	n

<tbody>
</tbody>

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