How to Fix COUNTA Returning 1 When FILTER Finds No Matches in Excel
Question details
The user needs to get an accurate count of 0 when an Excel FILTER formula finds no matches, avoiding the issue where COUNTA incorrectly returns 1 due to the error output.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Counting the number of rows or unique values returned by a FILTER function based on multiple criteria.
- Observed behavior
- When no rows match the FILTER criteria, the function outputs an error. The COUNTA function evaluates this error as a non-empty cell and incorrectly returns a count of 1 instead of 0.
Verify the exact data ranges you are using in your formula. To prevent calculation lag, identify your specific data range (e.g., A2:A1000) rather than using entire column references (e.g., A:A).
Use IFNA and ROWS to Count Filtered Data Accurately
Replace the COUNTA function with ROWS and force the FILTER function to return an #N/A error when empty, which can then be converted to a 0 using IFNA.
When the FILTER function yields no results, it outputs an error (like #CALC!) by default. Because COUNTA counts any cell that is not strictly blank—including cells with errors—it counts the error as 1.
By structuring your formula to deliberately return an #N/A error using the NA() function when empty, you can wrap the calculation in IFNA to gracefully return a 0. Counting with ROWS instead of COUNTA ensures the logic evaluates the array size correctly.
Inside your FILTER function, set the third argument (if_empty) to NA(). Example: FILTER(A2:A100, B2:B100=1, NA()).
If you are trying to count distinct values, nest the FILTER function inside the UNIQUE function: UNIQUE(FILTER(A2:A100, B2:B100=1, NA())).
Wrap the previous formula in the ROWS function to count the number of returned rows rather than checking for non-empty cells: ROWS(UNIQUE(FILTER(A2:A100, B2:B100=1, NA()))).
Finally, wrap the entire formula in IFNA to return 0 if the #N/A error is triggered. The final formula will look like this: =IFNA(ROWS(UNIQUE(FILTER(A2:A100, (B2:B100=1)*(C2:C100=2), NA()))), 0).
Press Enter to apply the formula. It will now accurately display 0 when your filter criteria do not find any matching data.
Master Advanced Formulas with WPS Spreadsheet
WPS Spreadsheet fully supports dynamic array formulas, including FILTER, UNIQUE, and IFNA. You can seamlessly execute complex data filtering and counting tasks just like in Microsoft Excel.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office and open your dataset containing the data you want to filter and count.
- 2. Select the target cell: Click on the cell where you want the filtered count result to appear.
- 3. Input the array formula: Type the formula =IFNA(ROWS(FILTER(range, criteria, NA())), 0) customized to your data ranges.
- 4. Calculate the result: Press Enter. WPS Spreadsheet will instantly calculate the array and return the exact count, accurately displaying 0 for empty filter results.
Frequently Asked Questions
Why does COUNTA return 1 for a blank FILTER result?
If the FILTER function finds no matching data based on your criteria, it defaults to returning an error (such as #CALC!). The COUNTA function counts any cell that is not completely blank, which means it counts the error text as 1 item.
Can I use COUNTIFS instead of FILTER and COUNTA?
Yes, for straightforward counting based on multiple criteria, COUNTIFS is often a better and more efficient choice. However, if you need to count UNIQUE filtered values, you cannot use COUNTIFS, and must rely on the combination of UNIQUE, FILTER, ROWS, and IFNA.
What does the NA() function do in the FILTER formula?
The NA() function deliberately generates an #N/A error. By placing it in the 'if_empty' argument of the FILTER function, you force an #N/A error when no matches are found. This specific error is then easily caught and converted to 0 by the IFNA function.
Does WPS Spreadsheet support dynamic array functions?
Yes, modern versions of WPS Spreadsheet fully support dynamic array functions, including FILTER, UNIQUE, SORT, and SEQUENCE, allowing you to build and analyze advanced data models effortlessly.
