Remove blank rows from a range dynamically with FILTER (all-blank vs key-blank)

Critical distinction: removing ALL-blank spacer rows vs missing-ID rows

Filtering on OrderID alone excludes the bank-fee row even though it has a description and amount. Add the three nonempty tests to retain a row containing data in any selected column.

The formulas create a compact result; they do not delete source rows. A formula returning "" is treated as empty by this test, while an ordinary space is nonempty. Zero is data and should be retained.

For counting, sum the row flags directly. ROWS(FILTER(...)) errors when nothing matches, and ROWS of a text fallback counts that fallback as one rather than zero.

Source errors need separate handling; they are not silently treated as blank.

Count rows with at least one populated field

Each column is populated if it is nonempty or numeric. The numeric check explicitly retains zero. Convert the total per row to one TRUE/FALSE flag before summing.

=SUMPRODUCT(((((A2:A5<>"")+ISNUMBER(A2:A5))+((B2:B5<>"")+ISNUMBER(B2:B5))+((C2:C5<>"")+ISNUMBER(C2:C5)))>0)*1)

Count rows with a populated OrderID

Use A2:A5 <> "" when records missing a primary key should be excluded from downstream joins.

=SUMPRODUCT((((A2:A5<>"")+ISNUMBER(A2:A5))>0)*1)

Display the compact table

Place this formula in an empty area with space for the returned rows.

=FILTER(A2:C5,(((A2:A5<>"")+ISNUMBER(A2:A5))+((B2:B5<>"")+ISNUMBER(B2:B5))+((C2:C5<>"")+ISNUMBER(C2:C5)))>0,"No populated rows")

Further reading

• Microsoft Support — FILTER function