How to Find Maximum Value in Excel with Condition: A Step-by-Step Guide

Finding the maximum value in Excel with a condition is a common task that can be tackled easily with functions like MAX and IF. You can use the MAXIFS function (available in Excel 2016 and later) to find the highest value that meets a specified condition. Here’s a quick guide to help you accomplish this:

Finding Maximum Value in Excel with Condition

In this section, we’ll walk through the process of finding the maximum value in Excel based on a condition. By following these steps, you will be able to identify the highest value in a dataset that meets your criteria. This is especially useful for sorting through large sets of data for specific insights.

Step 1: Open Your Excel Workbook

Start by opening the Excel workbook that contains your data.

Make sure the data is organized clearly, with columns and rows labeled appropriately. It’s easier to find the maximum value if your data is structured neatly.

Step 2: Select the Cell for the Result

Choose an empty cell where you want the maximum value to be displayed.

This helps in keeping your results separate from the raw data, which makes your workbook cleaner and more readable.

Step 3: Enter the MAXIFS Function

Type =MAXIFS( in the selected cell to start the function.

The MAXIFS function allows you to specify the range of values and the condition they need to meet.

Step 4: Choose the Range for Evaluation

Add the range of cells you want to find the maximum value from. For example, A1:A10.

This range should contain the numerical values you are evaluating. Make sure the range is accurate to avoid errors.

Step 5: Define the Condition Range

Next, choose the range that contains the conditions you want to apply. For instance, B1:B10.

This range corresponds to the criteria that the maximum value must meet. It should be of the same size as the evaluation range.

Step 6: Set the Condition

Enter the condition in the MAXIFS function. For example, ">=100".

This condition tells Excel to only consider values that meet this criterion when finding the maximum.

Step 7: Close the Function and Press Enter

Finish by closing the function with a parenthesis ) and press Enter.

Excel will now display the maximum value that meets your specified condition in the selected cell.

After completing these steps, Excel will show you the highest value in the specified range that also meets the given condition. This can be incredibly handy for data analysis and reporting.

Tips for Finding Maximum Value in Excel with Condition

  • Use named ranges to make your formulas easier to read and manage.
  • Double-check your ranges to ensure they are of equal length.
  • Combine MAXIFS with other functions like AVERAGEIFS for more complex analyses.
  • Make use of conditional formatting to visually highlight maximum values.
  • Practice with sample data to become familiar with the function before applying it to important datasets.

Frequently Asked Questions

Can I use MAXIFS in older versions of Excel?

No, the MAXIFS function is only available in Excel 2016 and later. For older versions, you can use an array formula with MAX and IF.

What if my condition is text-based?

You can still use MAXIFS with text conditions. For example, MAXIFS(A1:A10, B1:B10, "Apples") will find the maximum value in A1:A10 where B1:B10 is "Apples".

How do I handle errors in MAXIFS?

Ensure your ranges are the same size and properly formatted. Use the IFERROR function to manage errors gracefully.

Can MAXIFS handle multiple conditions?

Yes, you can add multiple condition ranges and criteria to the MAXIFS function. For example, MAXIFS(A1:A10, B1:B10, ">=100", C1:C10, "<=200").

What if my data changes?

The MAXIFS function updates automatically when your data changes, ensuring your analysis is always current.


  1. Open your Excel workbook.
  2. Select the cell for the result.
  3. Enter the MAXIFS function.
  4. Choose the range for evaluation.
  5. Define the condition range.
  6. Set the condition.
  7. Close the function and press Enter.


Finding the maximum value in Excel with a condition might seem challenging at first, but with the MAXIFS function, it’s a breeze. By following the steps outlined above, you can easily locate the highest value in your dataset that meets specific criteria. This skill is invaluable for data analysis, allowing you to extract meaningful insights quickly and efficiently.

Whether you're a student, professional, or just someone who loves working with data, mastering this function will definitely elevate your Excel game. Remember, practice makes perfect. So, go ahead and experiment with different datasets and conditions. Happy analyzing!

Get Our Free Newsletter

How-to guides and tech deals

You may opt out at any time.
Read our Privacy Policy