max if multiple criteria

[doylzer]

hi all,

I am trying to use Max function but only if it matches multiple criteria (3 criteria)

I have attached my example to this.

I want find the max of my criteria A10:C10 from the table above.

I would like to get the max number in cell D10 (the answer should be 90 in this example)

[Glenn Kennedy]

Hi there. Try this array formula:

=MAX(IF($A$2:$A$7=A10,IF($B$2:$B$7=$B$10,IF($C$2:$C$7=$C$10,$D$2:$D$7))))

Array Formulae are a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly braces yourself - it won't work...

[sweep]

Hi,

Does this work for you?


=MAX(IF(($A$3:$A$7=A10)*($B$3:$B$7=B10)*($C$3:$C$7=C10),$D$3:$D$7,""))

CTRL, SHIFT and ENTER to confirm

[bmouse]

Hello! Try this out

Here is the code itself:

Please Login or Register  to view this content.

EDIT:
Nevermind, the code is wrong, as it doesn't look for maximum values just the criteria. Somehow I missed that part