Count unique values in a column in Excel

Asked 3/9, 2013 at 7:46 Answered 30/5 at 15:41

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 enter image description here

Perisarc answered 3/9, 2013 at 7:46 Comment(3)

Can you not just use a pivot table for the answer? – Collegian 3/9, 2013 at 7:51

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. – Perisarc 3/9, 2013 at 7:54

can you accept the most upvoted answer? I think it's better – Caruthers 13/5, 2016 at 13:28

110

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.

Belldas answered 3/9, 2013 at 9:3 Comment(5)

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 – Belldas 4/12, 2013 at 14:48

This should be the "better" answer. Would be nice if it were marked so. – Sampling 21/5, 2014 at 14:35

Works fine but damn that slows up a sheet holy crap – Equipage 22/9, 2014 at 15:42

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 ;) – Equipage 22/9, 2014 at 19:13

Sorry for commenting on an old post but be aware, if you have lots of rows (e.g. 240k, in my case) then this answer will lock up your computer. After about a minute I gave up and killed Excel in the task manager. – Dispassionate 28/6 at 12:21

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)

Bohr answered 3/9, 2013 at 8:31 Comment(3)

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? – Koph 19/5, 2014 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. – Mehta 14/3, 2016 at 22:6

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 – Eboni 10/1, 2023 at 17:2

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.

Pietro answered 3/9, 2013 at 21:24 Comment(2)

This one wins the "method most likely to be used by normal people" award. :) – Minify 3/11, 2015 at 10:4

This is much faster than barry houdini's answer for me, with approx. 1 million rows. – Folkestone 3/2, 2016 at 15:20

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

Orcus answered 19/3, 2014 at 16:37 Comment(3)

This is the most elegant I've seen, but like all the others does not do exact string matching. – Cursed 29/4, 2014 at 10:34

I love the twisted thinking underlying this formula. – Doubledecker 6/2, 2015 at 0:24

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. – Unquiet 10/7, 2015 at 14:23

With the Dynamic Array formulas:

=COUNTA(UNIQUE(A:A))

Juggle answered 17/10, 2019 at 17:12 Comment(0)

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

Neolatin answered 14/4, 2014 at 12:15 Comment(0)

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.

Bellbottoms answered 30/6, 2015 at 20:46 Comment(1)

Nicely efficient for a quick count! Note that you want to filter on cells with No fill. – Chippy 15/3, 2019 at 19:7

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.

Melainemelamed answered 3/9, 2013 at 8:36 Comment(0)

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/

Tenant answered 5/6, 2015 at 16:20 Comment(0)

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.

Hyperphysical answered 30/5 at 15:41 Comment(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.

Synsepalous answered 28/8, 2015 at 7:9 Comment(0)

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.

Griffon answered 19/1, 2016 at 8:36 Comment(0)

-1

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.

Leven answered 18/11, 2014 at 23:46 Comment(1)

when you don't understand something, try to comment – Byblow 18/11, 2014 at 23:49

Hot tags

Godot Unity Godot Help Programming Godot 4.X GUI GDScript 3D 2D Physics CSharp Godot 3.X VR XR Projects C++

Recommended topics

Hot tags