How to Use the MAXIFS Formula for Worst-Case Values in Excel
Question details
The user needs to identify the highest (worst-case) value for specific building stacks based on building identifiers, without manually sorting or calculating the data.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Extracting the maximum conditional value from a dataset containing multiple building and stack records.
- Observed behavior
- The user requires an automated formula approach rather than manually renaming or evaluating values to find the maximum stack value per building.
Ensure your dataset is well-organized into columns, with clear identifiers for your categories (e.g., building names) and numerical values that you want to evaluate.
Apply the MAXIFS Formula to Find the Maximum Value
The MAXIFS function is the most efficient way to return the maximum value in a range that meets one or more specified criteria.
The MAXIFS function evaluates a range of values and returns the highest number based on the conditions you set. In this scenario, it evaluates the values column against the building stack identifier column to pinpoint the worst-case value.
Click on the cell where you want the maximum worst-case value to be displayed.
Type `=MAXIFS(` to begin constructing the function.
Select or type the `max_range` (the column containing your numerical stack values, e.g., `F:F`), followed by a comma.
Select or type the `criteria_range1` (the column containing your building identifiers, e.g., `A:A`), followed by a comma.
Enter the `criteria1` (the specific building identifier or a cell reference containing it, e.g., `M6`), and close the parentheses `)`.
Press Enter to calculate the result. The final formula should look similar to `=MAXIFS(F:F, A:A, M6)`.

Use a MAX and IF Array Formula (For Older Excel Versions)
If you are using a version of Excel older than 2019 that does not support MAXIFS, you can combine the MAX and IF functions as an array formula.
Simplify Conditional Calculations with WPS Spreadsheet
WPS Spreadsheet offers robust, native support for complex functions like MAXIFS, making it incredibly easy to evaluate large datasets and extract conditional maximum values automatically without performance lags.
- 1. Open your dataset: Launch WPS Spreadsheet and open the workbook containing your building and stack data.
- 2. Access the Formula tab: Click on the cell where the result is needed and navigate to the 'Formulas' tab on the top ribbon.
- 3. Insert the function: Click 'Insert Function', search for 'MAXIFS', and input your value range, criteria range, and specific criteria using the intuitive dialog box.
- 4. Generate the result: Click OK to instantly generate your worst-case values without manually writing out the formula syntax.

Frequently Asked Questions
What should I do if my MAXIFS formula returns a #VALUE! error?
This usually happens if the size of the `max_range` and `criteria_range` do not match. Ensure that both ranges cover the exact same number of rows (for example, F2:F100 and A2:A100 must align perfectly).
Can I use multiple criteria with the MAXIFS function?
Yes, the MAXIFS function can handle up to 126 pairs of `criteria_range` and `criteria`. Simply add them sequentially in the formula, separated by commas (e.g., `=MAXIFS(F:F, A:A, M6, B:B, "Level 1")`).
Does MAXIFS include hidden rows in its calculation?
Yes, MAXIFS evaluates all cells in the specified ranges, including those located in hidden rows or columns. To exclude hidden rows from your maximum value calculation, you would need to use a more complex array formula utilizing the AGGREGATE function.




