How to get unique values based on a condition in Excel
Formula (Excel and Google Sheets)
=SORT(UNIQUE(FILTER(B2:B12,C2:C12=G1)))Filter the rows first, then remove the repeats: =SORT(UNIQUE(FILTER(B2:B12,C2:C12=G1))) lists each person who logged time on one project.
- Result on the example
- a 3-row list that spills from G2 in
G2 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Same formula
See it work
The formula is already in G2. Change any input and the result updates, or edit the formula to experiment.
How it works
FILTER keeps the employees from Apollo rows only, which includes Raj three times and Lee twice. UNIQUE removes those repeats, and SORT orders the names. Change the project in G1 and the team list changes with it.
=SORT(UNIQUE(FILTER(B2:B12,C2:C12=G1)))Result{3 rows × 1 col} "Ana Ruiz", "Lee Chen", "Raj Patel"
B2:B1211 rows × 1 col: "Raj Patel", "Ana Ruiz", "Lee Chen", "Raj Patel", …A range: 11 cells in one column.
C2:C1211 rows × 1 col: "Apollo", "Borealis", "Apollo", "Apollo", …A range: 11 cells in one column.
G1"Apollo"The value in G1.
C2:C12=G1{11 rows × 1 col} TRUE, FALSE, TRUE, TRUE, …The = operator tests whether the two sides are equal.
FILTER(B2:B12,C2:C12=G1){6 rows × 1 col} "Raj Patel", "Lee Chen", "Raj Patel", "Ana Ruiz", …FILTER: Rows of a range that meet every condition. #N/A when none match.- range
B2:B1211 rows × 1 col: "Raj Patel", "Ana Ruiz", "Lee Chen", "Raj Patel", … - condition1
C2:C12=G1{11 rows × 1 col} TRUE, FALSE, TRUE, TRUE, …
- range
UNIQUE(FILTER(B2:B12,C2:C12=G1)){3 rows × 1 col} "Raj Patel", "Lee Chen", "Ana Ruiz"UNIQUE: Distinct rows of a range. Spills.- array
FILTER(B2:B12,C2:C12=G1){6 rows × 1 col} "Raj Patel", "Lee Chen", "Raj Patel", "Ana Ruiz", …
- array
SORT(UNIQUE(FILTER(B2:B12,C2:C12=G1))){3 rows × 1 col} "Ana Ruiz", "Lee Chen", "Raj Patel"SORT: Sorts rows by one or more columns. is_ascending is TRUE or FALSE.- range
UNIQUE(FILTER(B2:B12,C2:C12=G1)){3 rows × 1 col} "Raj Patel", "Lee Chen", "Ana Ruiz"
- range
In Google Sheets
The same formula works in Google Sheets: =SORT(UNIQUE(FILTER(B2:B12,C2:C12=G1))). We ran it in Google Sheets mode on the same data and it returns the same results, spilling 3 rows.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =UNIQUE(FILTER(B2:B12,C2:C12=G1)) | spills 3 × 1Different result | Without SORT, names appear in the order they first logged time. Raj Patel, Lee Chen, Ana Ruiz. |
| =SORT(UNIQUE(FILTER(B2:B12,(C2:C12=G1)*(D2:D12>=5)))) | spills 2 × 1Different result | Add conditions by multiplying them inside FILTER. Only people with an Apollo entry of 5 hours or more. |
Common mistakes
- Putting UNIQUE inside FILTER,
FILTER(UNIQUE(B2:B12),…), fails: the unique list is shorter than the condition column. - If no row matches, FILTER returns #CALC!. Add a third argument:
FILTER(B2:B12,C2:C12=G1,"None").
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Get unique values based on a condition
G2, list each employee who logged hours on the project in G1, once each, A to Z.Questions
How do I count them instead of listing them?
Count the rows of the list: =ROWS(UNIQUE(FILTER(B2:B12,C2:C12=G1))).
How do I put the list in one cell?
Join it: =TEXTJOIN(", ",TRUE,SORT(UNIQUE(FILTER(B2:B12,C2:C12=G1)))).
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.