How to count unique values across multiple columns in Excel
Formula (Excel and Google Sheets)
=COUNTA(UNIQUE(TOCOL(B2:D6,1)))Stack the columns into one with TOCOL, skipping blanks, then count the unique values: =COUNTA(UNIQUE(TOCOL(B2:D6,1))).
- Result on the example
- 7 in
G1 - Works in
- Excel 2024 and Microsoft 365
- Google Sheets
- Same formula
See it work
The formula is already in G1. Change any input and the result updates, or edit the formula to experiment.
How it works
UNIQUE compares whole rows when it gets several columns, so it can't count unique names across columns directly. TOCOL first turns the block into a single column, and its second argument, 1, drops the blank cells. UNIQUE then keeps each name once, and COUNTA counts them: 7 people.
=COUNTA(UNIQUE(TOCOL(B2:D6,1)))Result7
B2:D65 rows × 3 cols: "Ana", "Ben", "Ana", "Cara", …A range: 5 rows × 3 columns.
TOCOL(B2:D6,1){13 rows × 1 col} "Ana", "Ben", "Ana", "Cara", …TOCOL: An array or range as one column, optionally skipping blanks and errors.- array
B2:D65 rows × 3 cols: "Ana", "Ben", "Ana", "Cara", … - ignore
1
- array
UNIQUE(TOCOL(B2:D6,1)){7 rows × 1 col} "Ana", "Ben", "Cara", "Dev", …UNIQUE: Distinct rows of a range. Spills.- array
TOCOL(B2:D6,1){13 rows × 1 col} "Ana", "Ben", "Ana", "Cara", …
- array
COUNTA(UNIQUE(TOCOL(B2:D6,1)))7COUNTA: Counts cells that are not empty.- value1
UNIQUE(TOCOL(B2:D6,1)){7 rows × 1 col} "Ana", "Ben", "Cara", "Dev", …
- value1
In Google Sheets
The same formula works in Google Sheets: =COUNTA(UNIQUE(TOCOL(B2:D6,1))). We ran it in Google Sheets mode on the same data and it returns 7.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUMPRODUCT((B2:D6<>"")/COUNTIF(B2:D6,B2:D6&"")) | 7 | Works in every Excel version: each name adds 1/n for each of its n cells, and blanks add nothing. |
| =COUNTUNIQUE(B2:D6)Google Sheets | 7 | Google Sheets only: COUNTUNIQUE accepts a block of several columns. |
Common mistakes
COUNTA(UNIQUE(B2:D6))treats each row as one value and counts the cells of the unique rows, which is not the number of different names.- Without the 1 in TOCOL, the blank cells become one extra "value" in the count.
- TOCOL needs Excel 2024 or Microsoft 365; use the SUMPRODUCT version in older versions.
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.
Count unique values across multiple columns
G1, count how many different people attended any of the three sessions in B2:D6.Questions
How do I list the unique names across the columns?
Use =UNIQUE(TOCOL(B2:D6,1)), or wrap it in SORT for alphabetical order.
How do I count how many sessions each person attended?
Next to the list, use =COUNTIF($B$2:$D$6,F2).
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.