How to Count Nonblank FILTER Results Without Empty Strings in Excel
Question details
The user wants to count the number of valid records returned by the FILTER function without mistakenly counting empty text strings or error values.
- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Counting filtered data records where the if_empty argument of the FILTER function returns an empty string, which usually trips up standard counting formulas.
- Observed behavior
- The COUNTA function evaluates an empty string ("") or an error as a valid entry, incorrectly returning 1 even when the FILTER function finds no matching records.
Ensure that your data ranges and criteria ranges have the exact same dimensions before nesting the FILTER function inside other formulas. This prevents #CALC! or #VALUE! errors that can skew your counting results.
Use IFERROR to Handle Empty Results with COUNTA and FILTER
Wrap your COUNTA and FILTER combination inside an IFERROR function to return exactly 0 when no matching records are found.
The most robust way to solve this issue is by intentionally omitting the if_empty argument in your FILTER function. By doing this, FILTER will return an error when it finds no matches. We can then use IFERROR to catch that error and force the formula to output 0 instead of counting an empty string.
Click on the blank cell where you want the final count of your filtered records to be displayed.
Write your basic FILTER function without the if_empty argument. For example: FILTER(A2:A10, B2:B10="Criteria"). This ensures the formula returns an error if no records match.
Enclose the FILTER function within a COUNTA function to count the returned array of items. Type: COUNTA(FILTER(A2:A10, B2:B10="Criteria")).
Finally, wrap the entire formula in IFERROR to output a zero if the FILTER fails to find matches. The final formula should look like this: =IFERROR(COUNTA(FILTER(A2:A10, B2:B10="Criteria")), 0).
Press Enter to apply. If required by a legacy Excel version, press Ctrl+Shift+Enter to evaluate it as an array formula.
Use IF and ISERROR for Strict Error Checking
An alternative approach using IF and ISERROR to explicitly check if the FILTER function yields an error before attempting to count the output.
Easily Analyze Data with Array Functions in WPS Spreadsheet
WPS Spreadsheet offers full support for advanced dynamic array functions like FILTER, COUNTA, and IFERROR, allowing you to seamlessly process complex data structures without compatibility issues.
- 1. Open WPS Spreadsheet: Launch WPS Office and open your workbook containing the data to be filtered.
- 2. Input your data: Ensure your dataset is organized in clear columns without merged cells.
- 3. Apply the nested formula: Type =IFERROR(COUNTA(FILTER(range, criteria)), 0) in your desired results cell and press Enter. WPS Spreadsheet handles the dynamic array automatically.
Frequently Asked Questions
Why does COUNTA return 1 when FILTER finds no matches?
When the FILTER function doesn't find any matches and uses an empty string ("") as its if_empty argument, COUNTA sees this empty text string as a valid, non-blank entry and counts it as 1. COUNTA counts any cell that is not completely empty, including formulas that return empty text.
Can I use COUNTBLANK instead of COUNTA to solve this?
No, COUNTBLANK specifically counts empty cells in a specified range. To count the number of valid records returned by a FILTER array, you need to count non-blank cells, which is exactly what COUNTA does, provided you use an error-handling wrapper like IFERROR to bypass the empty string issue.
Do I need to press Ctrl+Shift+Enter for the FILTER function?
In newer versions of Excel (Microsoft 365) and WPS Office that natively support dynamic arrays, you only need to press Enter. However, in older versions or specific legacy setups, you may need to press Ctrl+Shift+Enter to evaluate nested array formulas correctly.
What happens if my ranges in the FILTER function have different dimensions?
The FILTER function requires the return array and the criteria array to have matching row dimensions. If they differ, the formula will return a #VALUE! error. If you are using the IFERROR wrapper, it will catch this error and output 0, potentially masking your dimensional mistake. Always ensure your ranges align perfectly.
