# How to count unique values across multiple columns in Excel

Stack the columns into one with TOCOL, skipping blanks, then count the unique values: `=COUNTA(UNIQUE(TOCOL(B2:D6,1)))`.

**Excel:** `=COUNTA(UNIQUE(TOCOL(B2:D6,1)))` (Excel 2024 and Microsoft 365)

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

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| Workshop | Session 1 | Session 2 | Session 3 |  | Different people |
| Table 1 | Ana | Ben | Ana |  |  |
| Table 2 | Cara | Ana | Dev |  |  |
| Table 3 | Ben | Eli | Cara |  |  |
| Table 4 | Dev |  | Fay |  |  |
| Table 5 | Gus | Cara |  |  |  |

Formula in G1: `=COUNTA(UNIQUE(TOCOL(B2:D6,1)))`

Result: 7

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

## Other ways

- `=SUMPRODUCT((B2:D6<>"")/COUNTIF(B2:D6,B2:D6&""))`: 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 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.

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

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/count-unique-values-across-multiple-columns