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    lt   amp List  1  2    COUNTIF List    lt   amp List  1    COUNTIF List    lt   amp List  1  0     returns

User · Answer

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.

User · Answer

To count the number of different values in A2 A100  not counting blanks     SUMPRODUCT  A2 A100 lt  gt     COUNTIF A2 A100 A2 A100 amp          Copied from an answer by  Ulli Schmid to What is this COUNTIF   formula doing      SUMPRODUCT  A1 A100 lt  gt     COUNTIF A1 A100 A1 A100 amp        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 amp        1    1    2    apple    peach    apple                              so this  amp    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 amp        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 lt  gt      which is  1  1  1  1  1  1  0  0  0  0  0         by our intermediate result  COUNTIF A1 A100 A1 A100 amp      and sum up over the values   SUMPRODUCT  A1 A100 lt  gt     COUNTIF A1 A100 A1 A100 amp           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 amp      then some of those 0 94 would have been 0 0  As division by zero is not allowed  we would have thrown an error

User · Answer

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

User · Answer

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

User · Answer

You can add a new formula for unique record count   IF COUNTIF  A 2 A2 A2  gt 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

User · Answer

try -  SUM IF FREQUENCY MATCH COLUMNRANGE COLUMNRANGE 0  MATCH COLUMNRANGE COLUMNRANGE 0   gt 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   gt 0 1    Press Ctrl Shift Enter to make the formula an array  won t calculate correctly otherwise

User · Answer

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

User · Answer

With the Dynamic Array formulas as of this posting only available to Office 365 Insiders     COUNTA UNIQUE A A

User · Answer

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 lt     MATCH     amp D3 D786 D3 D786 amp    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

User · Answer

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 lt  gt      A2 A12 32  COUNTIF B2 B12 B2 B12

User · Answer

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

User · Answer

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

[excel] Count unique values in a column in Excel

Examples related to excel

Examples related to unique