# Thread: Formula for Max value with 2 criteria

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

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

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

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

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

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•