Function Problems

How to Fix MAXIFS or MINIFS Returning Zero in Excel

Guest Writer Oct 12, 2026 869 views

Question details

The user needs to find out why the MAXIFS and MINIFS functions are returning a zero on production records when they work correctly on test data.

How to Fix MAXIFS or MINIFS Returning Zero in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Searching for the earliest or latest time for a specific person, date, and SAP record within a large production dataset.
Observed behavior: The formulas calculate perfectly on test data but return zero on the actual production records, indicating a potential issue with the underlying data formats or mismatched criteria.

Before you start

Ensure that your calculation options are set to Automatic in the Formulas tab and verify that your production data does not contain hidden characters or leading spaces.

Solution 1Recommended

Convert Text-Formatted Dates and Numbers to Real Values

Use this solution if your production data was imported from another system (like SAP) and the numbers, dates, or times are stored as text, which causes mathematical functions to ignore them and return zero.

When Excel imports data from external databases, it often formats dates, times, and numbers as text. Functions like MAXIFS and MINIFS only evaluate actual numeric values. If the range you are evaluating contains text-formatted numbers, the function will ignore them and output a zero.

Select the problematic column

Highlight the entire column containing the dates, times, or values that your MAXIFS/MINIFS formula is evaluating.

Open the Text to Columns tool

Navigate to the 'Data' tab on the Excel ribbon and click on 'Text to Columns'.

Convert the data

In the wizard that appears, leave 'Delimited' selected and click 'Finish'. This forces Excel to re-evaluate the column and convert text strings back into standard numeric and date values.

Re-calculate the formula

Check your MAXIFS or MINIFS formula again to see if it now returns the correct minimum or maximum value instead of zero.

Convert Text-Formatted Dates and Numbers to Real Values

Data formatting tip: You can quickly identify text-formatted numbers by their alignment. By default, text aligns to the left side of a cell, while real numbers and dates align to the right.

Solution 2

Verify Formula Ranges and Criteria Matches

Use this solution to ensure your MINIFS and MAXIFS formulas are constructed correctly and that the range sizes perfectly match.

Free Excel Alternative

Calculate Advanced Formulas Flawlessly with WPS Spreadsheet

WPS Spreadsheet is a powerful, free alternative to Microsoft Excel that fully supports advanced data analysis functions like MAXIFS, MINIFS, and SUMIFS. It seamlessly processes large datasets imported from SAP and provides intuitive tools to quickly fix text-to-number formatting errors.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open your existing Excel workbook containing the production records.
2. Select your calculation cell: Click on the cell where you want to display the maximum or minimum value.
3. Input the function: Type =MAXIFS( or =MINIFS( and select your ranges and criteria. WPS Spreadsheet will provide real-time syntax suggestions.
4. Press Enter to calculate: Hit Enter to execute the formula. If your data contains text-formatted numbers, use the WPS 'Error Checking' smart tag to convert them to numbers instantly.

100% format compatibility with Microsoft Excel (.xlsx, .xls, .csv) filesBuilt-in error checking to instantly highlight text-formatted numbersRobust processing for large, complex production datasetsCompletely free and lightweight Office alternative

microsoft office alternative - wps office

Frequently Asked Questions

Why does my MAXIFS formula return 0 when I know the criteria match?

This almost always happens because the values in your max_range are formatted or stored as text rather than real numbers. The MAXIFS function ignores text, so if it finds no numeric values that meet the criteria, it defaults to returning 0.

How do I check if my Excel dates are stored as text?

You can use the ISTEXT function. In an empty cell, type =ISTEXT(A2) (assuming A2 is your date cell). If it returns TRUE, the date is stored as text. Alternatively, check the alignment: text defaults to the left, while real dates default to the right.

Do the criteria ranges in MINIFS need to be the same size?

Yes, all criteria ranges and the min_range must be exactly the same size. If they are different sizes (for example, evaluating B2:B100 against criteria in C2:C50), the formula will not evaluate correctly and usually returns a #VALUE! error.

Can trailing spaces cause MINIFS or MAXIFS to fail?

Yes. If your criteria is 'John' but the production data cell contains 'John ' (with a trailing space), the function will not recognize it as a match. You can use the TRIM function to clean your production data before applying MAXIFS or MINIFS.

Excel & Data Problems