Excel · worked task guide
Count Only Visible Blank Cells in Excel
Use a populated ID column to detect visible rows, then test the target cell separately. In D2, enter =SUBTOTAL(103,$A2)*--(LEN(C2)=0), fill down and sum the helper column. This counts visible empty cells and formulas returning an empty string; spaces and zeros do not count.
Filtering a list changes which rows you can see. COUNTBLANK(C2:C7) still tests its whole range. A visibility helper lets you count the blanks belonging to the current filter without deleting any records.
Copy a sample that exposes the common traps
Paste this into A1 on a spare sheet. The empty cells in C2 and C3 are intentional. C4 contains a formula returning empty text, C5 contains one space, and C6 contains the number zero. Every ID in column A is populated.
| Row | ID | Region | Result in C | LEN(C)=0 |
|---|---|---|---|---|
| 2 | 101 | East | Empty cell | TRUE |
| 3 | 102 | West | Empty cell | TRUE |
| 4 | 103 | East | ="" | TRUE |
| 5 | 104 | East | =" " | FALSE |
| 6 | 105 | East | 0 | FALSE |
| 7 | 106 | East | Ready | FALSE |
Add one helper per record
- Put Visible blank in D1. Enter the formula below in D2 and fill down through D7.
- Select A1:D7 and choose Data → Filter. Include the helper in the same records if you sort the list.
- Outside the filtered range, enter
=SUM(D2:D7), for example in F2. - Filter Region to East. F2 should show 2. Filter to West instead: it should show 1.
=SUBTOTAL(103,$A2)*--(LEN(C2)=0)SUBTOTAL(103,$A2) returns a visibility flag for the populated ID in that row. LEN(C2)=0 tests the target cell; the two minus signs convert TRUE or FALSE to 1 or 0. Their product is 1 only when both checks pass. Use SUM on these flags, rather than nesting another SUBTOTAL around the helper formulas.
The visibility reference must be populated in every record. Pointing it at the blank target itself would return zero precisely where you need to count a blank. Create a fixed ID or other populated marker column if your list does not already have one.
Check filtering and manual hiding separately
| Visible records | LEN-based helper total | Reason |
|---|---|---|
| All rows | 3 | Two empty cells plus the empty-string formula. |
| East only | 2 | ID 101 and ID 103 qualify. |
| East only, manually hide row 4 | 1 | Function number 103 also excludes manually hidden rows. |
| West only, all manual hiding cleared | 1 | ID 102 is the only visible record. |
Unhide row 4 before returning to the full list. If your workbook uses manual calculation, recalculate before checking the totals. Function number 3 includes manually hidden rows; 103 excludes them. Both exclude rows hidden by a filter, as documented in Microsoft’s SUBTOTAL reference.
Choose what “blank” means
The main formula treats an empty cell and ="" as blank. COUNTBLANK also counts empty-string results, but does not provide the visibility test used here. LEN counts spaces as characters, so a single space stays outside this count.
If you mean only cells with no content or formula at all, replace the blank test with ISBLANK:
=SUBTOTAL(103,$A2)*--ISBLANK(C2)That version returns 2 for the full sample and 1 for East. It excludes the formula in C4 even though the formula displays nothing. Neither version treats an error value as an empty cell; investigate unexpected errors instead of hiding them in the total.
These formulas use functions documented for Excel 2016 and later. They do not require dynamic arrays or an array-entry keystroke. Extend both the helper and the total ranges when adding records. For an Excel table named Table1, keep the helper header Visible blank and use =SUM(Table1[Visible blank]) outside the table; its structured reference includes new helper rows.
Documentation reviewed 4 October 2026. The sample’s expected counts were checked independently; this page does not claim execution in desktop Excel.