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
The answer is
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.
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/
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.
I copied and pasted my dataset into a different sheet to verify it and it's worked for me.
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.
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.
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)
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
Dataand thenFilter
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.
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.
With the Dynamic Array formulas(as of this posting only available to Office 365 Insiders):
=COUNTA(UNIQUE(A:A))
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))
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
Source: Stackoverflow.com