Count unique values in a column in Excel

Question

I have an .xls file with a column of data. How do I count how many unique values are contained in this column?

I have googled many options, but the formulas I've found always give me errors. For example,

=INDEX(List, MATCH(MIN(IF(COUNTIF($B$1:B1, List)=0, 1, MAX((COUNTIF(List, "<"&List)+1)*2))*(COUNTIF(List, "<"&List)+1)), COUNTIF(List, "<"&List)+1, 0))

returns

Answer 1

With the Dynamic Array formulas:

=COUNTA(UNIQUE(A:A))

Answer 2

The answer has evolved but people are holding on to older tried and true methods.

COUNTA(UNIQUE(A:A))

Unique will dump the distinct data in rows below the formula. But if you just wrap in with COUNTA(magic!). It will not. I did see the answer above after I posted, but I want to stress it is available to almost all excel users.

Answer 3

To count the number of different values in A2:A100 (not counting blanks):

=SUMPRODUCT((A2:A100<>"")/COUNTIF(A2:A100,A2:A100&""))

---

Copied from an answer by @Ulli Schmid to What is this COUNTIF() formula doing?:

=SUMPRODUCT((A1:A100<>"")/COUNTIF(A1:A100,A1:A100&""))

Counts unique cells within A1:A100, excluding blank cells and ones with an empty string ("").

How does it do that? Example:

A1:A100 = [1, 1, 2, "apple", "peach", "apple", "", "", -, -, -, ...]
then:
A1:A100&"" = ["1", "1", "2", "apple", "peach", "apple", "", "", "", "", "", ...]

so this &"" is needed to turn blank cells (-) into empty strings (""). If you were to count directly using blank cells, COUNTIF() returns 0. Using the trick, both "" and - are counted as the same:

COUNTIF(A1:A100,A1:A100) = [2, 2, 1, 2, 1, 2, 94, 94, 0, 0, 0, ...]
but:
COUNTIF(A1:A100,A1:A100&"") = [2, 2, 1, 2, 1, 2, 94, 94, 94, 94, 94, ...]

If we now want to get the count of all unique cells, excluding blanks and "", we can divide

(A1:A100<>""), which is [1, 1, 1, 1, 1, 1, 0, 0, 0, 0, 0, ...]

by our intermediate result, COUNTIF(A1:A100,A1:A100&""), and sum up over the values.

SUMPRODUCT((A1:A100<>"")/COUNTIF(A1:A100,A1:A100&""))  
= (1/2 + 1/2 + 1/1 + 1/2 + 1/1 + 1/2 + 0/94 + 0/94 + 0/94 + 0/94 + 0/94 + ...)
= 4

Had we used COUNTIF(A1:A100,A1:A100) instead of COUNTIF(A1:A100,A1:A100&""), then some of those 0/94 would have been 0/0. As division by zero is not allowed, we would have thrown an error.

Answer 4

I am using a spreadsheet with headers in row 1, data are in rows 2 and below.

IDs are in column A. To count how many different values there are I put this formula from row 2 to the end of the spreadsheet of the first available column [F in my case] : "=IF(A2=A1,F1+1,1)".

Then I use the following formula in a free cell: "=COUNTIF(F:F,1)". In this way I am sure every ID is counted.

Please note that IDs must be sorted, otherwise they will be counted more than once...but unlike array formulas it is very fast even with a 150000 rows spreadsheet.

Answer 5

You can add a new formula for unique record count

=IF(COUNTIF($A$2:A2,A2)>1,0,1)

Now you can use a pivot table and get a SUM of unique record count. This solution works best if you have two or more rows where the same value exist, but you want the pivot table to report an unique count.

Answer 6

Another tricky way that just occurred to me (tested and it worked!).

• Select the data in the column
• In the menu, select Conditional Formatting, Highlight Cells, Duplicate Values
• Select whether you want to highlight unique or duplicate values.
• Save the highlight
• Select the data
• Go to Data and then Filter

Filter based on color:

Admittedly, this is more for one-off checks of data than a spreadsheet you'll use often, since it requires some formatting changes.

Answer 7

If using a Mac

1. highlight column
2. copy
3. open terminal.app
4. type pbpaste|sort -u|wc -l

Linux users replace pbpaste with xclip xsel or similar

Windows users, it's possible but would take some scripting... start with http://brianreiter.org/2010/09/03/copy-and-paste-with-clipboard-from-powershell/

Answer 8

My data set is D3:D786, Column headings in D2, function in D1. Formula will ignore blank values.

=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(D3,ROW(D3:D786)-ROW(D3),,1)),IF(D3:D786<>"",MATCH("~"&D3:D786,D3:D786&"",0))),ROW(D3:D786)-ROW(D3)+1),1))

When entering the formula, CTRL + SHIFT + ENTER

I found this at the site below, there's more explanations there about Excel that i didn't understand, if you're into that sort of thing.

http://www.mrexcel.com/forum/excel-questions/553903-count-unique-values-filtered-column.html#post2735467

I copied and pasted my dataset into a different sheet to verify it and it's worked for me.

Answer 9

try - =SUM(IF(FREQUENCY(MATCH(COLUMNRANGE,COLUMNRANGE,0),MATCH(COLUMNRANGE,COLUMNRANGE,0))>0,1))

where COLUMNRANGE = the range where you have these values.

e.g. - =SUM(IF(FREQUENCY(MATCH(C12:C26,C12:C26,0),MATCH(C12:C26,C12:C26,0))>0,1))

Press Ctrl+Shift+Enter to make the formula an array (won't calculate correctly otherwise)

Answer 10

Count unique with a condition. Col A is ID and using condition ID=32, Col B is Name and we are trying to count the unique names for a particular ID

=SUMPRODUCT((B2:B12<>"")*(A2:A12=32)/COUNTIF(B2:B12,B2:B12))

Answer 11

Here's an elegant array formula (which I found here http://www.excel-easy.com/examples/count-unique-values.html) that does the trick nicely:

Type

=SUM(1/COUNTIF(List,List))

and confirm with CTRL-SHIFT-ENTER

Answer 12

Here’s another quickie way to get the unique value count, as well as to get the unique values. Copy the column you care about into another worksheet, then select the entire column. Click on Data -> Remove Duplicates -> OK. This removes all duplicated values.

Answer 13

You can do the following steps:

1. First isolate the column (by inserting a blank column before and/or after the column you want to count the unique values if there are any adjacent columns;

2. Then select the whole column, go to 'Data' > 'Advanced Filter' and check the checkbox 'Unique records only'. This will hide all non-unique records so you can count the unique ones by selecting the whole column.