How to count unique values that match a condition
✓ Verified in LibreOffice 25.8.7.3 ✓ Verified in Google Sheets (2026-08-30)Count distinct entries in one column, but only for rows matching a criterion in another.
The formula
| App | Formula | Notes |
|---|---|---|
| Excel (desktop) | =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. We then ran the same formulas in Google Sheets, executed 2026-08-30: a formula-only workbook goes into Google Drive, which converts it to a Sheet and recalculates every formula with Google’s own engine, and comes back out as .xlsx carrying the values Google computed. It returned 3 for the worked example, the same value LibreOffice produced. Both engines’ numbers on this page are executed results. The Excel formula follows Microsoft’s official documented syntax — we do not run desktop Excel.
Functions used
ROWS · UNIQUE · FILTER — see full Excel, Google Sheets & LibreOffice compatibility for each.
Related recipes
- How to average a range that contains errors
- How to combine two lists without duplicates
- How to count the number of unique values in a range
- How to FILTER by multiple criteria (AND / OR)
- How to find values that appear in both of two lists