Logical & Lookup Formulas
Check if All Cells in a Range Are Blank (COUNTBLANK vs COUNTA)
Test whether an entire row or multi-column block is completely blank using COUNTBLANK(B2:E2) = COLUMNS(B2:E2) (including formula empty strings "") vs strict COUNTA(B2:E2) = 0.
Why COUNTA = 0 fails when upstream formulas return ""—and COUNTBLANK succeeds!
Before suppressing empty line items on an invoice or archiving inactive accounts, spreadsheet authors often check =COUNTA(B2:E2) = 0. That works on raw manual cells—until a single cell contains =IF(A2="", "", VLOOKUP(...)) returning an empty string "", which COUNTA counts as 1 non-empty formula and falsely marks the row "Active"!
Comparing COUNTBLANK(B2:E2) against total cell capacity ROWS(B2:E2) * COLUMNS(B2:E2) solves that trap cleanly because COUNTBLANK counts BOTH truly empty cells AND formula cells evaluating to ""! Across a 4-quarter revenue row B2:E2: when all 4 quarters are blank, COUNTBLANK(B2:E2) = COLUMNS(B2:E2) returns TRUE ("All Blank — Skip Row"); as soon as even a single quarter holds 0 or $450, COUNTBLANK drops below 4 and triggers active processing!
| Row State Across B2:E2 (4 Quarters) | COUNTA(B2:E2) = 0 | COUNTBLANK(B2:E2) = 4 | Invoice Print Action |
|---|---|---|---|
| All 4 Cells Truly Empty | TRUE | TRUE (4 = 4) | All Blank — Suppress Row |
| Formula ="" in B2, Others Empty | FALSE (Counts "")! | TRUE (4 = 4) | Visually Blank — Suppress Row |
| Q3 = 0 or $450, Others Empty | FALSE | FALSE (3 < 4) | Active Row — Include |
Test Whether Every Cell in a Row Range Is Visually Blank
Compares COUNTBLANK(B2:E2) against dynamic range size ROWS(B2:E2) * COLUMNS(B2:E2) so adding or removing columns never breaks a hardcoded count.
=IF(COUNTBLANK(B2:E2) = ROWS(B2:E2) * COLUMNS(B2:E2), "All Blank", "Has Data")Count Populated (Non-Blank) Entries Across the Range
Subtracts COUNTBLANK from total cell capacity to count genuinely visible entries while ignoring "" formula strings.
=ROWS(B2:E2) * COLUMNS(B2:E2) - COUNTBLANK(B2:E2)Try the example
Use the original values or change them to test your understanding.