Count Nonblank Values in Excel When IFERROR Returns an Empty String
Question details
Count cells with displayed values while excluding formulas that return an empty string.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet range contains IFERROR or other formulas that return an empty string when there is no value to display.
- Observed behavior
- Nonblank counting criteria may include formula cells that appear empty.
Identify the range to count and check whether it contains error values. The formula below counts zero and whitespace as values and includes cells in hidden rows.
Count displayed values with SUMPRODUCT and LEN
Use this formula to exclude both truly empty cells and formulas that return an empty string.
In Excel, click an empty cell outside the range you want to count.
Type =SUMPRODUCT(--(LEN(A1:A10&"")>0)), replacing A1:A10 with your range, and press Enter.
Compare the count with the values in the selected range. Cells containing only spaces and cells containing zero are included.
Count nonblank formula results in WPS Spreadsheets
Use SUMPRODUCT and LEN in WPS Spreadsheets to count values while excluding empty strings returned by IFERROR.
- 1. Open the spreadsheet: Open WPS Spreadsheets and select File > Open to choose your workbook.
- 2. Choose a result cell: Click an empty cell outside the range you want to count.
- 3. Enter the counting formula: Type =SUMPRODUCT(--(LEN(A1:A10&"")>0)), replace A1:A10 with your range, and press Enter.
- 4. Review the count: Check the result against the range. Zero and spaces count as values, and hidden rows are included.

Frequently Asked Questions
Why does COUNTA count a formula that looks blank?
COUNTA counts a cell containing a formula even when the formula returns an empty string. Use =SUMPRODUCT(--(LEN(A1:A10&"")>0)) to exclude empty-string results.
Can I subtract COUNTIF(range, "") from COUNTA(range)?
That can give an incorrect result when the range contains truly empty cells: COUNTA excludes those cells, but COUNTIF with an empty-string criterion can count them. The SUMPRODUCT and LEN formula avoids that mismatch.
Does the formula count cells that contain only spaces?
Yes. LEN treats spaces as characters, so a cell containing spaces is included in the count.
Does this count exclude hidden rows?
No. The formula checks cell contents, not row visibility, so it includes matching cells in hidden or filtered-out rows.




