Multiple Criteria Array Formula

[PGD15]

Hi there,

I am looking to write a multiple criteria array formula and I am stuggling.

I want to say from the values in column a that equal are an exact match to the word june (or a cell reference),
choose the largest value in column D which is not equal to zero, but has to be from June's data field.

This issue I am having is returning the largest value.

effectively im trying find a formula for a document which updates daily and I only want to see the largest 'sales' value from june.

due to the nature I cannot share the document

 

[Jonmo1]

Try this entered as an array with CTRL + SHIFT + ENTER

=MAX(IF(A1:A20="June",IF(D1:D20<>0,D1:D20)))

 

[steve the fish]

Theres this depending on your version of excel too:

=MAXIFS(D:D,A:A,"June")

 

[PGD15]

Brillant thank you this works a treat! Many thanks!

 

[Jonmo1]

You're welcome.