Count unique values in a column in Excel

Question

I have an .xls file with a column with some data. How do I count how many unique values contains this column?

I have googled many options, but the formulas they give there 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 enter image description here

Actually I don't want to save the answer. I just want to check how many unique values contains the column, because this file is later imported in `MySQL` and I want to see if it's imported correctly. — Ivanka Todorova, Sep 03 '13 at 07:54

score 110 · Accepted Answer · edited May 23 '17 at 12:10

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.

Note: I edited again - separators are determined by regional settings, so comma works for me in UK (and also works in USA) but ; may apply elsewhere, e.g. mainland Europe — barry houdini, Dec 04 '13 at 14:48
This should be the "better" answer. Would be nice if it were marked so. — DynamicDan, May 21 '14 at 14:35
Turns out I was trying to check for the entire column and that slows down the process so much that a macro that takes 1 second took a good 10 minutes. After checking with clients requirement the max number of rows was 156 so I set the query to check between 1 and 250. Works perfectly. Thanks houdini ;) — Tascalator, Sep 22 '14 at 19:13

score 16 · Answer 2 · edited Oct 21 '14 at 21:54

16

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)

edited Oct 21 '14 at 21:54

Community

1
1

answered Sep 03 '13 at 08:31

Nilesh Deshmukh

359
2
8

1

For some reason, this didn't work for me. My regional settings mandated I should use ';' as an argument separator and '.' as a decimal separator, but it still wasn't working. I'm using Excel 2013...maybe the API has changed? – ravemir May 19 '14 at 14:51
Works for me. I'm using Excel 2013. I also didn't need to make the formula an array for it to work. – Mike Mar 14 '16 at 22:06
I just tried about half a dozen different answers found in various places, and this is the only one that would count unique text values – claudekennilol Jan 10 '23 at 17:02

score 11 · Answer 3 · answered Sep 03 '13 at 21:24

11

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.

answered Sep 03 '13 at 21:24

Arconath

423
3
8

1

This one wins the "method most likely to be used by normal people" award. :) – estephan500 Nov 03 '15 at 10:04
This is much faster than barry houdini's [answer](http://stackoverflow.com/a/18588144/1399438) for me, with approx. 1 million rows. – StockB Feb 03 '16 at 15:20

score 10 · Answer 4 · answered Mar 19 '14 at 16:37

10

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

answered Mar 19 '14 at 16:37

Your Excel Guy

101
1
2

1

This is the most elegant I've seen, but like all the others does not do exact string matching. – Sam Brightman Apr 29 '14 at 10:34
1

I love the twisted thinking underlying this formula. – MattClarke Feb 06 '15 at 00:24
1

Works great on short lists. Chokes on long ones. I've got a 42000 row list that's been calculating for over five minutes and not done yet. 200 rows, imperceptible lag. 42000, waiting, waiting, waiting... Probably time to move it over to a database or some statistical app. – adamek Jul 10 '15 at 14:23

score 3 · Answer 5 · edited Apr 14 '14 at 12:31

3

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))

edited Apr 14 '14 at 12:31

Andrea

11,801
17
65
72

answered Apr 14 '14 at 12:15

Derek

191
1
2
9

score 2 · Answer 6 · answered Jun 30 '15 at 20:46

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:

Excel -- 2013 at least -- lets you filter on color. Sweet!

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

Nicely efficient for a quick count! Note that you want to filter on cells with No fill. — cpilko, Mar 15 '19 at 19:07

score 2 · Answer 7 · answered Oct 17 '19 at 17:12

2

With the Dynamic Array formulas(as of this posting only available to Office 365 Insiders):

=COUNTA(UNIQUE(A:A))

answered Oct 17 '19 at 17:12

Scott Craner

148,073
10
49
81

score 1 · Answer 8 · answered Sep 03 '13 at 08:36

You can do the following steps:

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

score 1 · Answer 9 · answered Jun 05 '15 at 16:20

If using a Mac

highlight column
copy
open terminal.app
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/

score 0 · Answer 10 · edited Aug 28 '15 at 07:41

0

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.

edited Aug 28 '15 at 07:41

Galma88

2,398
6
29
50

answered Aug 28 '15 at 07:09

Henry Tran

1

score 0 · Answer 11 · edited Jan 19 '16 at 09:13

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.

score -1 · Answer 12 · answered Nov 18 '14 at 23:46

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.

when you don't understand something, try to comment – chouaib Nov 18 '14 at 23:49 — chouaib, Nov 18 '14 at 23:49

Count unique values in a column in Excel

12 Answers12

Linked

Related