Formula for Max value with 2 criteria

Topic

New Reply

I’m not sure how to combine a max formula with an if statement. I have 2 columns, one column has M or F (male or female) in it, the other has Open or Masters in it. I need to find the maximum value in COL C for Males, Females, Males in Open division, Females in Open division, Males in Masters division and Females in masters division. I’m not sure of the best way to construct the formula. Thank you for the help.

Reply | Quote

Replies

Let’s say the data are in rows 2:11. You can use formulas like this:

=MAX(IF(($A$2:$A$11=”M”)*($B$2:$B$11=”Open”),$C$2:$C$11))

This is an array formula, i.e. confirm with Ctrl+Shift+Enter.

See the attached sample workbook.

Reply | Quote

thanks for the formula Hans. Does the “*” between the 2 criteria mean “and”, is there an “or” operator?

Reply | Quote

The * is multiplication, but in this example it acts as “AND”. You’d use + (addition) to act as “OR”.

Reply | Quote

ok, thanks for the explanation.

Now, I have the max formula working and then i tried to copy it to the next row and change max to MIN. The help screen says to use the fx bar but it doesn’t show the word “MAX”, it starts with the IF.

I did click the fx and the braces disappeared and i changed MAX to MIN and then hit ctl+shift+enter but it changed both the formulas to MIN. I have the max formula in B6 and want the min formula in B7.

Reply | Quote

If you select both cells, edit the formula and press Ctrl+Shift+Enter, you’ll enter the same array formula in both cells. You can undo this by pressing Ctrl+Z.
Make sure that only B7 is selected when you edit the formula.

Reply | Quote