Chapter 11 · intermediate · 5 min
Duplicates and distinct counts: COUNTIF, UNIQUE
Two related but different questions come up constantly: "which rows are duplicates?" and "how many distinct values are there?" COUNTIF answers the first by counting how many times each value shows up; UNIQUE answers the second by giving you the distinct list to count.
The grid
A1:A5 holds a list of department names with repeats — Ops and Finance each appear twice.
COUNTIF: flag which rows are duplicates
=COUNTIF(A$1:A$5,A1)>1COUNTIF counts how many times A1's value appears across the whole range. If that count is more than 1, the value shows up somewhere else too — it's a duplicate. The $ on the range keeps it pinned to A1:A5 as this formula is copied down the column; only the comparison cell (A1) should change per row.
"Ops" appears in rows 1 and 3. Does COUNTIF(A$1:A$5,A1)>1 return TRUE or FALSE for row 1?
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Ops | =COUNTIF(A$1:A$5,A1)>1 | ||
| 2 | Finance | |||
| 3 | Ops | |||
| 4 | IT | |||
| 5 | Finance |
UNIQUE + COUNTA: how many distinct values
UNIQUE(range) returns the distinct values in a range. Wrapped in COUNTA, you get a single number — the distinct count — rather than a spilled list:
=COUNTA(UNIQUE(A1:A5))A1:A5 has 5 entries but only 3 distinct values (Ops, Finance, IT).
The 5 rows are Ops, Finance, Ops, IT, Finance. How many distinct departments is that?
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Ops | =COUNTA(UNIQUE(A1:A5)) | ||
| 2 | Finance | |||
| 3 | Ops | |||
| 4 | IT | |||
| 5 | Finance |
The trap: ROWS(UNIQUE(...)) instead of COUNTA
ROWS() normally counts the rows in a range, so =ROWS(UNIQUE(A1:A5)) looks like the obvious way to count a distinct list. In this engine it evaluates to #VALUE! instead — ROWS doesn't handle a dynamic array result the way it handles a plain range reference. COUNTA(UNIQUE(...)) is the reliable form; don't reach for ROWS here even though it reads more naturally.
COUNTIF as a running "first occurrence" flag
Expanding the range as you copy a formula down — instead of pinning it with $ — turns COUNTIF into a growing window. =COUNTIF(A$1:A1,A1)=1 only counts rows from the top down to the current row, so it's TRUE exactly once per distinct value: the first time it appears.
Is this the first time this value has appeared?
In D1, flag whether row 1's value is the first occurrence, scanning from the top down rather than the whole column.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Ops | |||
| 2 | Finance | |||
| 3 | Ops | |||
| 4 | IT | |||
| 5 | Finance |