13.3 C
London
Sunday, August 17, 2025
HomeExcelDescriptive Statistics in ExcelExcel: How to Use MAX IF with Multiple Criteria

Excel: How to Use MAX IF with Multiple Criteria

Related stories

Learn About Opening an Automobile Repair Shop in India

Starting a car repair shop is quite a good...

Unlocking the Power: Embracing the Benefits of Tax-Free Investing

  Unlocking the Power: Embracing the Benefits of Tax-Free Investing For...

Income Splitting in Canada for 2023

  Income Splitting in Canada for 2023 The federal government’s expanded...

Can I Deduct Home Office Expenses on my Tax Return 2023?

Can I Deduct Home Office Expenses on my Tax...

Canadian Tax – Personal Tax Deadline 2022

  Canadian Tax – Personal Tax Deadline 2022 Resources and Tools...

You can use the following basic syntax to use MAX IF with multiple criteria in Excel:

=MAX(IF(A2:A11="Mavs", IF(B2:B11="Forward", C2:C11)))

This particular formula finds the max value in the range C2:C11 where the corresponding value in the range A2:A11 is equal to “Mavs” and the corresponding value in the range B2:B11 is equal to “Forward.”

The following examples shows how to use this syntax in practice.

Example: MAX IF with Multiple Criteria in Excel

Suppose we have the following dataset in Excel that shows the points scored by various basketball players:

We can type the following formula into cell E2 to calculate the max value of players who are on the Mavs team and have a position of Forward:

=MAX(IF(A2:A11="Mavs", IF(B2:B11="Forward", C2:C11)))

The following screenshot shows how to use this formula in practice:

We can see that the player with the max value for the Points column with a Team value of “Mavs” and Position value of “Forward” is 30.

We can modify the values in quotation marks in the formula to find the max points among players who meet different criteria.

For example, we can type the following formula into cell E2 to calculate the max value of players who are on the Heat team and have a position of Guard:

=MAX(IF(A2:A11="Heat", IF(B2:B11="Guard", C2:C11)))

The following screenshot shows how to use this formula in practice:

The player with the max value for the Points column with a Team value of “Heat” and Position value of “Guard” is 21.

Additional Resources

The following tutorials explain how to perform other common tasks in Excel:

How to Find Max Value by Group in Excel
How to Use a RANK IF Formula in Excel

Subscribe

- Never miss a story with notifications

- Gain full access to our premium content

- Browse free from up to 5 devices at once

Latest stories