Function Problems

Count Nonblank Values in Excel When IFERROR Returns an Empty String

Kushani NimanthikaKushani Nimanthika Nov 14, 2026 868 views

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.
Before you start

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.

Solution 1Recommended

Count displayed values with SUMPRODUCT and LEN

Use this formula to exclude both truly empty cells and formulas that return an empty string.

1
Select the result cell

In Excel, click an empty cell outside the range you want to count.

2
Enter the formula

Type =SUMPRODUCT(--(LEN(A1:A10&"")>0)), replacing A1:A10 with your range, and press Enter.

3
Check the result

Compare the count with the values in the selected range. Cells containing only spaces and cells containing zero are included.

Check for errors and hidden rows: An unhandled error in the range can cause the counting formula to return an error. The formula also counts cells in hidden rows; it does not filter by row visibility.
Count displayed values in WPS Spreadsheets

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. 1. Open the spreadsheet: Open WPS Spreadsheets and select File > Open to choose your workbook.
  2. 2. Choose a result cell: Click an empty cell outside the range you want to count.
  3. 3. Enter the counting formula: Type =SUMPRODUCT(--(LEN(A1:A10&"")>0)), replace A1:A10 with your range, and press Enter.
  4. 4. Review the count: Check the result against the range. Zero and spaces count as values, and hidden rows are included.
Exclude formula results that display as empty stringsOpen and edit Microsoft Excel XLS and XLSX filesUse familiar spreadsheet functions and formulas
microsoft office alternative - wps office

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.