How to count unique values that match a condition
✓ Verified in LibreOffice 25.8.7.3Count distinct entries in one column, but only for rows matching a criterion in another.
The formula
| App | Formula | Notes |
|---|---|---|
| Excel | =ROWS(UNIQUE(FILTER(A2:A7,B2:B7="East"))) | Excel 365/2021. COUNTA can replace ROWS if the source has no blanks. |
| Google Sheets | =ROWS(UNIQUE(FILTER(A2:A7,B2:B7="East"))) | Sheets also has =COUNTUNIQUEIFS(A2:A7,B2:B7,"East") built in. |
| LibreOffice Calc | =ROWS(UNIQUE(FILTER(A2:A7,B2:B7="East"))) | Needs LibreOffice 24.8+ (UNIQUE and FILTER). |
How it works
Working inside-out: FILTER keeps the names on "East" rows (alice, alice, carol, bob), UNIQUE collapses the duplicates (alice, carol, bob), and ROWS counts them — 3 distinct people. The classic pre-dynamic-array alternative =SUMPRODUCT((B2:B7="East")/COUNTIFS(A2:A7,A2:A7,B2:B7,B2:B7)) still works in older versions but is much harder to reason about.
Verified, not just documented
We ran =ROWS(UNIQUE(FILTER(A2:A7,B2:B7="East"))) in LibreOffice 25.8.7.3 (headless, with forced recalculation) and it returned 3 — exactly the expected result. Every formula here is confirmed by actually executing it.