# How to get unique values based on a condition in Excel

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.

**Excel:** `=SORT(UNIQUE(FILTER(B2:B12,C2:C12=G1)))` (Excel 2021, 2024 and Microsoft 365)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F | G |
| --- | --- | --- | --- | --- | --- | --- |
| Date | Employee | Project | Hours |  | Project | Apollo |
| 2026-09-01 | Raj Patel | Apollo | 6.5 |  | Team |  |
| 2026-09-01 | Ana Ruiz | Borealis | 4 |  |  |  |
| 2026-09-02 | Lee Chen | Apollo | 7 |  |  |  |
| 2026-09-02 | Raj Patel | Apollo | 5 |  |  |  |
| 2026-09-03 | Kim Ortiz | Cobalt | 8 |  |  |  |
| 2026-09-03 | Ana Ruiz | Apollo | 3.5 |  |  |  |
| 2026-09-04 | Lee Chen | Borealis | 6 |  |  |  |
| 2026-09-04 | Lee Chen | Apollo | 2 |  |  |  |
| 2026-09-05 | Mo Diaz | Cobalt | 7.5 |  |  |  |
| 2026-09-05 | Kim Ortiz | Borealis | 4 |  |  |  |
| 2026-09-06 | Raj Patel | Apollo | 6 |  |  |  |

Formula in G2: `=SORT(UNIQUE(FILTER(B2:B12,C2:C12=G1)))`

Result (spills 3 × 1): Ana Ruiz; Lee Chen; Raj Patel

## 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.

## Other ways

- `=UNIQUE(FILTER(B2:B12,C2:C12=G1))`: 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))))`: 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")`.

## 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))))`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/unique-values-based-on-a-condition