Function Problems

How to Fix MINIFS and MAXIFS Functions Not Working in Excel

Maira Mehtab Sep 22, 2026 871 views

Question details

The user is experiencing issues where the MINIFS and MAXIFS functions are failing to calculate or return expected results in Microsoft 365 Excel.

Product: Microsoft 365 Excel
Device & OS: not provided
Scenario: Calculating minimum or maximum values across a dataset based on multiple specific criteria using MINIFS or MAXIFS formulas.
Observed behavior: The formulas do not work as expected, likely returning an error like #VALUE! or #NAME?, or yielding incorrect/zero results due to underlying data or syntax issues.

Before you start

Before troubleshooting the formulas, verify that the dataset does not contain hidden trailing spaces and that all referenced ranges have the exact same number of rows and columns.

Solution 1Recommended

Ensure All Reference Ranges Are the Same Size

The most common reason MINIFS and MAXIFS return a #VALUE! error is mismatched range sizes between the calculation range and the criteria ranges.

In Excel, functions that evaluate multiple conditions require symmetrical data arrays. If your target range spans 100 rows, every criteria range in that formula must also span exactly 100 rows.

Select the formula cell

Click on the cell containing the MINIFS or MAXIFS formula that is returning an error.

Inspect the formula bar

Click into the formula bar at the top of the worksheet to highlight the referenced ranges.

Align range dimensions

Check the row numbers. For example, if your formula is =MINIFS(C2:C100, A2:A99, "Yes"), change A2:A99 to A2:A100 so it perfectly matches the size of the min_range.

Apply changes

Press Enter to update the formula and clear the #VALUE! error.

Pro Tip: Using Excel Tables (Ctrl+T) automatically dynamically sizes your ranges, preventing dimension mismatch errors when adding new data.

Solution 2

Convert Numbers Stored as Text

If MINIFS or MAXIFS returns a 0 when it should return a value, the numbers in your target range might be formatted as text.

Easily Calculate Conditional Minimums and Maximums with WPS Spreadsheet

WPS Spreadsheet fully supports advanced logical formulas like MINIFS and MAXIFS. If you are struggling with Excel errors, you can seamlessly open your workbook in WPS Office and perform complex multi-condition calculations with built-in formula error checking.

1. Open your workbook: Launch WPS Spreadsheet and open your existing .xlsx file.
2. Insert the formula: Select an empty cell and type =MINIFS( or =MAXIFS( to trigger the formula helper.
3. Select your ranges: Follow the on-screen tooltip to select your min/max range, followed by your criteria ranges.
4. Calculate the result: Press Enter to instantly calculate the conditional value.

100% compatibility with Microsoft Excel formulas and .xlsx filesNative support for advanced functions including MINIFS, MAXIFS, and XLOOKUPBuilt-in formula syntax checking to easily spot range mismatchesFree, lightweight, and fast alternative to Microsoft 365

Frequently Asked Questions

Why is my MINIFS formula returning 0?

This usually happens if none of the rows meet all the criteria you specified, or if the numbers in your calculation range are formatted as text. Check your criteria logic and ensure the target range contains valid numeric values.

What causes the #VALUE! error in MAXIFS?

The #VALUE! error typically occurs when the size and shape of the max_range do not perfectly match the criteria_range1, criteria_range2, etc. Ensure all referenced ranges have the exact same number of rows and columns.

Are MINIFS and MAXIFS available in older versions of Excel?

No, MINIFS and MAXIFS were introduced in Excel 2019 and Microsoft 365. If you open the file in Excel 2016 or older, the formula will return a #NAME? error. Using a compatible alternative like WPS Office ensures these functions work perfectly without needing a premium upgrade.

Excel & Data Problems