Dynamic arrays
Count unique values in a column while ignoring blank cells
Pair UNIQUE with FILTER and ROWS to count distinct customers or SKUs without counting empty rows as an extra item.
Why bare ROWS(UNIQUE(range)) overcounts by 1 when blanks exist
When a range contains no blanks, ROWS(UNIQUE(A2:A5)) returns the exact distinct count. As soon as the range includes even one empty cell, UNIQUE treats the blank (output as 0 in Excel or "" in Arte Sheets) as its own distinct entry—inflating the count by 1!
Filtering out empty slots before calling UNIQUE guarantees an exact count of real distinct items.
| Row | Client (A) | Included in Distinct Count? |
|---|---|---|
| 2 | Acme | Yes (#1) |
| 3 | Globex | Yes (#2) |
| 4 | Acme | Duplicate — skipped |
| 5 | (blank) | Excluded by FILTER |
| 6 | Initech | Yes (#3) |
Blank-safe distinct count formula
Count the rows that satisfy the same filter condition before calling UNIQUE. If none qualify, return 0; this also handles formulas that return an empty string. The example is for text customer names: zero is excluded deliberately and must not be removed if it is a valid ID in your dataset.
=IF(SUMPRODUCT((A2:A6<>"")*(A2:A6<>0))=0, 0, ROWS(UNIQUE(FILTER(A2:A6, (A2:A6<>"")*(A2:A6<>0)))))Contrast with bare ROWS(UNIQUE(A2:A6))
Without FILTER, ROWS(UNIQUE(A2:A6)) returns 4 because it includes the blank row.
=ROWS(UNIQUE(A2:A6))Try the example
Use the original values or change them to test your understanding.