Formula Errors

Why Excel COUNTA Returns 1 for Blank Cells and How to Fix It

Muhammad Talha Oct 15, 2026 871 views

Question details

The user wants to know why the COUNTA function returns 1 when counting the results of UNIQUE and FILTER functions that yield no matching values, and how to correct this formula issue.

Why Excel COUNTA Returns 1 for Blank Cells and How to Fix It

Product: Excel
Device & OS: not provided
Scenario: Counting dynamic array results using COUNTA combined with UNIQUE and FILTER functions where no matching records exist.
Observed behavior: COUNTA returns 1 instead of 0 because it counts the calculation error generated by the FILTER or UNIQUE function when there are no matches to the criteria.

Before you start

Check your worksheet to confirm if the inner FILTER or UNIQUE function is returning a #CALC! or #N/A error when used alone without the COUNTA wrapper.

Solution 1Recommended

Wrap the Formula in an IFERROR Function

Use the IFERROR function to catch the calculation error returned by empty dynamic arrays and force the formula to output exactly 0.

The COUNTA function counts any non-empty value, including errors. When FILTER finds no matches, it generates an error (like #CALC!). COUNTA sees this error as a single item, resulting in an output of 1.

By wrapping your logic inside IFERROR, you can intercept the error before COUNTA processes it, or apply IFERROR to the entire expression to return 0 when an error occurs.

Select the target cell

Click on the cell containing your current COUNTA formula.

Edit the formula bar

Click into the formula bar at the top of the spreadsheet to edit your existing formula.

Add the IFERROR wrapper

Modify the formula to wrap the existing logic in IFERROR, setting the fallback value to 0. For example: =IFERROR(COUNTA(UNIQUE(FILTER($I$13:$I$330,($B$13:$B$330<>"")*($F$13:$F$330=B$2)))),0)

Apply the new formula

Press the Enter key to apply the changes. The cell should now properly display 0 when there are no matching values.

Alternative Array Handling: You can also use the 'if_empty' argument built directly into the FILTER function to return an empty array, which can sometimes prevent the initial error altogether depending on your Excel version.

Solution 2

Use a Combination of IF and ISERROR

Test if the UNIQUE or FILTER function evaluates to an error first, and return a custom blank or zero output if it does.

Solve Formula Errors in WPS Office

Easily Manage Dynamic Arrays and Formulas with WPS Spreadsheet

WPS Spreadsheet provides comprehensive support for dynamic array functions and complex formula combinations, allowing you to seamlessly process data and avoid common calculation pitfalls.

1. Open WPS Spreadsheet: Launch WPS Office and open your workbook containing the dynamic array formulas.
2. Locate the formula: Navigate to the cell that is improperly returning 1 instead of 0.
3. Update with IFERROR: In the formula bar, type =IFERROR( around your existing COUNTA formula, and add ,0) at the end.
4. Confirm the result: Press Enter to save the formula and ensure the cell updates to the correct value.

100% compatible with Microsoft Excel formulas including COUNTA, UNIQUE, and FILTER.Built-in error checking and formula auditing tools to quickly identify calculation issues.Free, lightweight, and features a familiar tabbed interface for seamless migration.

microsoft office alternative - wps office

Frequently Asked Questions

Why does COUNTA count empty cells with formulas?

COUNTA counts any cell that is not completely empty. If a cell contains a formula that returns an empty string (""), COUNTA still considers the cell to contain data and counts it as 1.

How can I count only cells that have visible text or numbers?

To ignore empty strings returned by formulas, you can use COUNTIF(range, "?*") to count only cells containing visible text, or use the COUNT function if you only need to count numeric values.

What does the FILTER function return when there are no matches?

If the FILTER function finds no records that match your criteria and you have not provided an 'if_empty' argument, it returns a #CALC! error. Surrounding functions like COUNTA will count this error as a valid item.

Excel & Data Problems