Cellwise Try it free

Excel guide · 2 min read

How to count unique values in Excel

Quick answerIn Excel 365 or 2021, use =COUNTA(UNIQUE(A2:A10)). UNIQUE lists each value once and COUNTA counts that list. In older versions, =SUMPRODUCT(1/COUNTIF(A2:A10,A2:A10)) gives the same count as long as the range has no blank cells.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

ABCD
1OrderCustomerCityTotal
2SO-1001Acme CoDenver420
3SO-1002BluebirdAustin180
4SO-1003Acme CoDenver75
5SO-1004Cedar LLCAustin960
6SO-1005BluebirdAustin240
7SO-1006Delta IncBoise310
8SO-1007Acme CoDenver55
9SO-1008Evergreen600
10SO-1009Cedar LLCBoise130

Method 1: COUNTA with UNIQUE (Excel 365 and 2021)

UNIQUE returns each customer once. COUNTA counts how many items that list contains. This is the simplest formula and it updates as you add orders.

=COUNTA(UNIQUE(B2:B10))

Result: 5

Returns 5: Acme Co, Bluebird, Cedar LLC, Delta Inc and Evergreen.

Ignore blank cells

UNIQUE treats an empty cell as a value, so the city column with one blank would be counted as 4 instead of 3. Filter out the blanks before you count.

=COUNTA(UNIQUE(FILTER(C2:C10,C2:C10<>"")))

Result: 3

Returns 3: Denver, Austin and Boise.

Count unique values that meet a condition

Put the condition inside FILTER. Here we count how many different customers placed an order over 200.

=COUNTA(UNIQUE(FILTER(B2:B10,D2:D10>200)))

Result: 5

Returns 5. Acme Co, Cedar LLC, Bluebird, Delta Inc and Evergreen each have at least one order over 200.

Method 2: SUMPRODUCT and COUNTIF (older Excel)

Before UNIQUE existed, people used =SUMPRODUCT(1/COUNTIF(B2:B10,B2:B10)). COUNTIF counts how often each value appears. A customer listed 3 times contributes 1/3 three times, which adds up to 1, so the total is the number of unique customers.

It returns #DIV/0! if the range contains blank cells, and it gets slow on tens of thousands of rows. Use it only when UNIQUE is not available.

Method 3: PivotTable distinct count (no formula)

A PivotTable can count distinct values per group, for example unique customers per city. You must add the data to the Data Model, or the Distinct Count option does not appear.

  1. Click inside the data and go to Insert > PivotTable.
  2. Tick "Add this data to the Data Model" and click OK.
  3. Drag City to Rows and Customer to Values.
  4. Click the Customer field in Values, choose Value Field Settings, pick Distinct Count, and click OK.

Unique vs distinct: two different counts

"Distinct" means every different value, counted once. That is what COUNTA(UNIQUE()) gives. Some people mean values that appear exactly once. For that, Excel 365 has a third UNIQUE argument: =COUNTA(UNIQUE(B2:B10,,TRUE)) returns 2 here (Delta Inc and Evergreen).

Common mistakes

Blanks inflate the count

Wrap the range in FILTER(range,range<>"") so empty cells are not counted as a value.

Using COUNT instead of COUNTA

COUNT only counts numbers, so it returns 0 for a list of names. Use COUNTA.

Values that look the same are counted twice

Trailing spaces make "Acme Co " different from "Acme Co". Clean the column with TRIM first.

#DIV/0! from the SUMPRODUCT formula

The range has blank cells. Use the UNIQUE method or shrink the range to the filled rows.

FAQ

What is the formula to count unique values in Excel?

Use =COUNTA(UNIQUE(range)) in Excel 365 or 2021. In older versions use =SUMPRODUCT(1/COUNTIF(range,range)) on a range with no blanks.

Is UNIQUE case-sensitive?

No. "acme co" and "Acme Co" count as one value. Remove Duplicates and COUNTIF also ignore case.

Why does COUNTA(UNIQUE()) return 1 for an empty list?

When FILTER finds nothing it returns an error or your if_empty text, and COUNTA counts that as one item. Use =IFERROR(ROWS(UNIQUE(FILTER(...))),0) to show 0.

Can I list the unique values as well as count them?

Yes. =UNIQUE(B2:B10) spills the list, and =SORT(UNIQUE(B2:B10)) sorts it alphabetically.

Functions used

Related guides

Updated