Formula Errors

Fix COUNTIFS Returning 0 and IFERROR Not Working in Excel

Olivia Miller Sep 28, 2026 869 views

Question details

The user is experiencing issues where COUNTIFS returns zero despite matching criteria existing, and IFERROR returns unexpected numeric values instead of handling errors.

How to Troubleshoot COUNTIFS and IFERROR Results in Excel

Product: Excel
Device & OS: not provided
Scenario: Calculating data matches using COUNTIFS and attempting to catch potential errors using IFERROR.
Observed behavior: COUNTIFS outputs 0 when valid matches exist, and IFERROR outputs regular numbers (like -45111) instead of the expected error replacement value.

Before you start

Before altering your formulas, ensure your spreadsheet's calculation options are set to 'Automatic' rather than 'Manual' so that formula results update in real-time.

Solution 1Recommended

Convert Text-Formatted Dates to Real Excel Dates

COUNTIFS often fails to match criteria if the source data contains dates or numbers stored as text. Converting them ensures the formula can properly read and evaluate the values.

A common reason for COUNTIFS returning 0 is a mismatch in data types. If your criteria is a date, but the cells in your lookup column are formatted as text, Excel will not recognize them as a match.

Select the data column

Highlight the column containing the dates or numbers you are trying to match with your COUNTIFS formula (for example, column AL).

Open Text to Columns

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

Apply Date formatting

Choose 'Delimited' and click 'Next' twice. In the third step, under 'Column data format', select 'Date' and click 'Finish' to convert the text into valid Excel dates.

Convert Text-Formatted Dates to Real Excel Dates

Verification: Once the conversion is complete, your COUNTIFS formula should automatically recalculate and display the correct count of matched records.

Solution 2

Identify the True Cause of Unexpected IFERROR Results

Understand that IFERROR only triggers on actual Excel error values. It does not evaluate standard numbers like 0 or negative numbers as errors.

Solution 3

Check for Circular References

Circular references can cause formulas to stall or return 0, interfering with both COUNTIFS and overall spreadsheet calculations.

WPS Spreadsheet

Fix and Analyze Formulas Easily with WPS Spreadsheet

WPS Office provides powerful and intuitive spreadsheet tools to help you accurately calculate data, troubleshoot formula errors, and manage large datasets seamlessly without the high cost.

1. Open your workbook: Launch WPS Spreadsheet and open your existing Excel workbook. Your formulas and data will be preserved seamlessly.
2. Diagnose errors: Navigate to the 'Formulas' tab and use the 'Error Checking' tool to diagnose cells returning unexpected results.
3. Fix data formats: Use the 'Text to Columns' feature under the 'Data' tab to correct any numbers or dates mistakenly stored as text.
4. Verify results: Press F9 to force a manual recalculation if needed, ensuring your COUNTIFS and IFERROR formulas display accurate values.

100% compatible with Microsoft Excel formats (.xlsx) and formulas like COUNTIFS and IFERROR.Built-in error checking and auditing tools to instantly identify calculation loops and issues.Advanced data formatting tools to effortlessly fix text-to-date mismatches.Free, lightweight, and features a familiar user interface for a smooth transition.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my COUNTIFS formula return 0 when I can see visual matches?

This usually happens due to data type mismatches, such as numbers or dates being stored as text. Excel requires exact data type matches. Using the 'Text to Columns' tool to convert text back into actual numbers or dates typically resolves this.

Can IFERROR replace a 0 or a negative number in Excel?

No, IFERROR only intercepts actual Excel error codes (like #N/A or #VALUE!). Because 0 and negative numbers are mathematically valid, IFERROR ignores them. To replace a 0, use an IF statement like =IF(A1=0, "Replacement Text", A1).

How do I find circular references in my spreadsheet?

Navigate to the 'Formulas' tab, click the drop-down arrow next to 'Error Checking', and hover over 'Circular References'. This will display a list of specific cells causing calculation loops in your workbook.

Does WPS Office support complex Excel functions like COUNTIFS?

Yes, WPS Spreadsheet is fully compatible with Microsoft Excel functions, including COUNTIFS, IFERROR, VLOOKUP, and XLOOKUP, ensuring your complex calculations work identically.

Excel & Data Problems