Counting Unique and Distinct Values in Excel

[nerdslife_team]

Hey everyone, ever find yourself needing to figure out how many unique entries you’ve got in an Excel sheet? Maybe you want to know how many customers only bought one thing, or how many students won exactly one award. It’s a common problem, and luckily, there are several ways to tackle it in Excel.

While Excel doesn’t have a single, dedicated function to count unique or distinct values, we can use a few different methods. Unique values are those that only appear once in your data, while distinct values are all the different values, including both those that appear once and those that are duplicated. Let’s explore some of the best approaches.

Method 1: Using Pivot Tables for Distinct Counts

If you’re using Excel 2013 or a later version, including Office 365, pivot tables are a really effective way to count distinct values.

1. First, select your data range and go to the “Insert” tab. Click on “PivotTable.”
   

   

2. In the “Create PivotTable” dialog, choose where you want the pivot table to be placed. Select either ‘New Worksheet’ or ‘Existing Worksheet’, and be sure to check the box that says ‘Add this data to the Data Model’. Click ‘OK’.
   

3. The PivotTable fields pane will appear on the right. Drag the column you want to count into the “Values” area. For example, let’s say it’s the “Name” field.
   

   

4. Click the little arrow next to “Count of Name” in the “Values” field and select “Value Field Settings…”.
   

   

5. In the “Value Field Settings” dialog, select “Distinct Count” from the list. Click “OK”.
   

6. You will now see the distinct count of your selected field displayed in the pivot table.
   

Method 2: Using SUMPRODUCT and COUNTIF for Distinct Values

Another great way to count distinct values involves using a combination of SUMPRODUCT and COUNTIF formulas.

1. The basic formula for distinct count is:

   =SUMPRODUCT(1/COUNTIF(data,data))
   

   where data is your cell range.

2. For example, if you want to count distinct values in the range A2:A10, you’d use:

   =SUMPRODUCT(1/COUNTIF(A2:A10,A2:A10))
   

3. Let’s break this down:

   • COUNTIF(A2:A10,A2:A10): This counts how many times each value appears in the range and returns an array like this: {1;4;1;4;2;1;4;2;4}.
   • 1/COUNTIF(A2:A10,A2:A10): This divides 1 by each value in the array, turning it into something like {1;0.25;1;0.25;0.5;1;0.25;0.5;0.25}.
   • SUMPRODUCT: This sums all the fractions in the array, giving you the count of distinct values.

4. This is how the formula result will be displayed:
   

   

Handling Blank Cells with SUMPRODUCT and COUNTIF

• If your data includes blank cells, you might encounter a #DIV/0! error. To include blank cells in the count, use this formula:

=SUMPRODUCT(1/COUNTIF(A2:A10,A2:A10&""))

Concatenating an empty string “” to the data range will include blank cells in the count.

• To exclude blank cells, use this formula:

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

  A2:A10<>"" will create a TRUE/FALSE array, where empty cells evaluate to FALSE and are excluded from the count.

  This is how the results of both formulas will be displayed.
  

  

Method 3: Using SUM and COUNTIF for Distinct Values

This method is very similar to the previous one but uses SUM instead of SUMPRODUCT and requires the formula to be entered as an array formula using Ctrl+Shift+Enter.

1. The basic syntax is:

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

2. Example:

   =SUM(1/COUNTIF(A2:A11,A2:A11))
   

3. After typing the formula, press Ctrl+Shift+Enter to execute it as an array formula. Excel will then automatically add curly brackets {} around the formula.

4. The SUM and COUNTIF function will return the distinct value count as 6.
   

Ignoring Blank Cells with SUM and COUNTIF

To ignore blank cells in your data when using SUM and COUNTIF, use the following formula:

=SUM(IF(A2:A11<>"",1/COUNTIF(A2:A11, A2:A11), 0))

Remember to press Ctrl+Shift+Enter to execute as an array formula.

This formula will exclude the empty cells from the count.

Counting Specific Types of Distinct Values (Text or Number)

• To count only distinct text values, use this array formula:

  =SUM(IF(ISTEXT(range),1/COUNTIF(range, range),""))
  

  For example:

  =SUM(IF(ISTEXT(A2:A12),1/COUNTIF(A2:A12, A2:A12),""))
  

  This will return the count of distinct text values in a list.
  

  

• To count only distinct numeric values (including dates and times which Excel stores as numbers), use this formula as an array formula:

  =SUM(IF(ISNUMBER(range),1/COUNTIF(range, range),""))
  

  For example:

  =SUM(IF(ISNUMBER(A2:A12),1/COUNTIF(A2:A12, A2:A12),""))
  

  This returns the count of distinct number values in a list.
  

  

Method 4: Counting Distinct Values with FREQUENCY and MATCH

If you are working with large datasets, you might find that using SUM and COUNTIF can slow down your worksheet. In that case, FREQUENCY and MATCH can offer better performance.

1. The formula syntax is:

    =SUM(IF(FREQUENCY(MATCH(A2:A11,A2:A11,0),MATCH(A2:A11,A2:A11,0))>0,1))
   

2. Make sure to press Ctrl+Shift+Enter to enter it as an array formula.

3. The MATCH function returns the position of each value. FREQUENCY then checks the occurrences and returns an array. Finally, SUM totals up the distinct values.
   

   

Method 5: Counting Distinct Rows Using COUNTIFS

If you want to count distinct rows of values across multiple columns, you can use the COUNTIFS function.

1. The syntax is:

   =SUM(1/COUNTIFS(A2:A12,A2:A12,B2:B12,B2:B12))
   

2. This formula counts the number of times each row appears across two columns, then sums the inverse of these counts, providing the number of distinct rows.
   

   

Counting Unique Values (Appearing Only Once)

Now, let’s explore how to find the number of unique values, meaning those that appear only once in your data.

Method 1: Using SUM, IF, and COUNTIF

1. The formula syntax for counting unique values:

    =SUM(IF(COUNTIF(range, range)=1,1,0))
   

   For example:

   =SUM(IF(COUNTIF(A2:A13,A2:A13)=1,1,0))
   

2. Remember to press Ctrl+Shift+Enter to enter it as an array formula.

3. The COUNTIF function counts the occurrences of each value. The IF function checks if the count equals 1. SUM adds up the unique counts.
   

   

Counting Specific Types of Unique Values (Text or Numbers)

• To count only unique text values, use this array formula:

   =SUM(IF(ISTEXT(A2:A13)*COUNTIF(A2:A13,A2:A13)=1,1,0))
  

  

• To count only unique numeric values, use this array formula:

  =SUM(IF(ISNUMBER(A2:A13)*COUNTIF(A2:A13,A2:A13)=1,1,0))
  

  

Case-Sensitive Unique Value Count

1. To count case-sensitive unique values, you first need a helper column. Enter this formula in the first cell of the column (e.g., B2):

   =IF(SUM((--EXACT($A$2:$A$12,A2)))=1,"Unique","Duplicate")
   

2. Press Ctrl+Shift+Enter and drag down to fill all the rows.
   
   
3. Then, in another cell use a COUNTIF formula to count the “Unique” entries:

   =COUNTIF(B2:B12,"Unique")
   

   

Counting Unique Rows

• To count unique rows, use the formula below, entered as an array formula using Ctrl+Shift+Enter:

  =SUM(IF(COUNTIFS(A2:A12,A2:A12,B2:B12,B2:B12)=1,1,0))
  

  

Method 6: Using the Advanced Filter for Unique Values

If you prefer not using formulas, the Advanced Filter option is a good choice for getting unique values.

1. First, select your range of cells. Go to the ‘Data’ tab, and click ‘Advanced’ in the ‘Sort & Filter’ group.
   

   

2. In the “Advanced Filter” dialog box, select the “Copy to another location” option and choose a cell to copy the filtered data.
   

3. Click on the ‘Copy to’ field and select an empty cell or range where you want to place the filtered unique values.
   

4. Check ‘Unique records only’, and press ‘OK’.
   

5. You will get a list of unique values from your source data.
   

   

6. You can then use the COUNTA formula to count these values.

   =COUNTA(D3:D12)
   

   

7. Alternatively, simply select the list of unique values, and look at the status bar (lower-right corner of Excel). It shows you the count.
   

   

Method 7: Removing Duplicates for Unique Value Count

Another way to get a unique count is by removing duplicate values directly.

1. First, copy your data to a new sheet or column to preserve your original data. Select any cell in the data range, go to the “Data” tab, and click “Remove Duplicates” in the “Data Tools” group.
   

   

2. In the “Remove Duplicates” box, select the column you want to remove duplicates from and press ‘OK’.
   

3. Excel will show you how many duplicate values were removed and how many unique values remain.
   

   

4. If you want to display the unique value count in a cell, you can use the COUNTA formula as shown in the Advanced Filter method above.

Note that removing duplicates leaves only one instance of values that appear multiple times. If you wish to count unique values that occur only once, then you have to combine several excel functions.

Method 8: Creating a User-Defined Function (UDF) for Unique Counts

If you find yourself needing to count unique values regularly, you can create a User-Defined Function (UDF).

1. Open Excel, go to the “Developer” tab, and click “Visual Basic.”
   

   

2. In the VBA window, right-click on “Microsoft Excel Objects”, click “Insert”, then “Module”.
   

   

3. Copy and paste the following code into the module window:

   Function CountUnique(DataRange As Range, CountBlanks As Boolean) As Integer
   Dim CellContent As Variant
   Dim UniqueValues As New Collection
   Application.Volatile
   On Error Resume Next
   For Each CellContent In DataRange
       If CountBlanks = True Or IsEmpty(CellContent) = False Then
           UniqueValues.Add CellContent, CStr(CellContent)
       End If
   Next
   CountUnique = UniqueValues.Count
   End Function
   

   

4. Click ‘File’ > ‘Save’ (or press Ctrl+S).

5. Now, you can use your custom function =COUNTUNIQUE(range, count_blanks).
   

6. range is the range of cells to check for unique values, and count_blanks is a boolean that specifies whether to include blank cells (TRUE) or not (FALSE).
   

Method 9: Using Helper Columns and COUNTIF

You can also count both unique and distinct values using helper columns along with the COUNTIF function

1. For unique values, add a helper column. In cell C2, enter this formula and drag it down:

   =COUNTIF($A$2:$A$12,A2)
   

2. For distinct values, add a helper column. In cell D2, enter this formula and drag it down:
excel =COUNTIF($A$2:A2,A2)

3. To count unique values, enter this formula:

    =COUNTIF(C2:C12,1)
   

   

4. To count distinct values, enter this formula:

   =COUNTIF(D2:D12,1)