Count unique values and distinct values

count-unique-values-excel-and-distinct-values

To count unique values in a list or range, use the UNIQUE function and configure the function’s third argument to TRUE.

In Microsoft 365 for Excel, you can create powerful Excel Formulas. Today’s guide will show you how to count unique and distinct values in a range using COUNT and UNIQUE functions.

Differences between unique values and distinct values

Before we start, we must consider the differences between “unique” and “distinct”.

Values that are considered “unique” are those that occur only a single time within a given range or dataset. In other words, a value is unique if it appears exactly once and is not repeated elsewhere in the range.

On the other hand, when we refer to “distinct” values, we are talking about a set of all different values present in the list or array. Each distinct value is listed only once, regardless of how many times it originally occurred in the range. Distinct values represent a collection of diverse elements without repetition.

The list below contains a range of values. To make the difference clear, we are using a simple example:

differences-between-unique-and-distinct-values

The data set contains the following values:

={1, 1, 2, 3, 4, 5}

If you want to get unique, you can do that based on the above definition. As you see, 1 is not a unique item.

={2, 3, 4, 5}

Here is the array that contains only different values; here is the list of distinct values:

={1, 2, 3, 4, 5}

It is easy to count these values because we are working with a small array. The next chapter will show you how to create a formula that uses COUNTIF and UNIQUE functions.

How to count unique values in Excel?

Here are the steps to count unique values in Excel:

  1. Open Excel.
  2. Type =COUNT(UNIQUE(B3:B11, FALSE, TRUE))
  3. Press Enter.
  4. The formula will return the count of unique values in the specified range, ignoring duplicates.

Example

In the first example, we want to write a formula to identify the unique and distinct values first, then count all matching values.

Unique values in rows or columns

If you want to create a list that contains unique records, set the 2nd argument to FALSE and use TRUE as the 3rd argument of the UNIQUE function. Finally, the COUNT function counts the matching values in the given column.

get-unique-values-from-a-column

Enter the formula in cell F3:

=UNIQUE(B3:B11, FALSE, TRUE)
=COUNT(UNIQUE(B3:B11, FALSE, TRUE)) //returns 3

To count unique values in a row, configure the arguments like this:

  • array: C2:K2
  • by_col: TRUE
  • exactly_once: TRUE
count-unique-values-in-a-row-list

The formula in cell C4:

=COUNT(UNIQUE(C2:K2, TRUE, TRUE)) //returns 3

How to count distinct values in Excel?

Steps to count distinct values in a range in Excel:

  1. Open Excel.
  2. Type =COUNT(UNIQUE(B3:B11, FALSE, FALSE))
  3. Press Enter.
  4. The formula will return the count of distinct values in the specified range.

Example: Distinct values in rows or columns

In the following two examples, we will count the distinct values in a row or a column. To get distinct values from a column, set the UNIQUE function’s second and third arguments to FALSE.

how-to-count-distinct-values-in-a-list

Enter the formula in cell F3:

=UNIQUE(B3:B11, FALSE, FALSE)
=COUNT(UNIQUE(B3:B11, FALSE, FALSE)) //returns 6

Now, modify the formula to get distinct values from a row.

count-distinct-values-in-a-row

The 2nd argument is TRUE; the 3rd argument is FALSE.

=UNIQUE(C2:K2, TRUE, FALSE)
=COUNT(UNIQUE(C2:K2, TRUE, FALSE)) //returns 6

Count unique values in a range

The UNIQUE function works fine if you look at and count unique or distinct values in a column or row. What if we have a range that contains multiple rows and columns?

Update: You can flatten multiple ranges using the TOCOL and TOROW functions.

FLATTEN function in Excel (Workaround)

The FLATTEN function is currently missing in Microsoft Excel and available only on Google Sheets. Its main advantage is that it flattens values into a single column. We are waiting for native support for Excel.

excel-flatten-function

We use the LAMBDA function to create the same functionality in Excel.

First, copy the following expression to the Clipboard. The formula uses the following functions: LAMBDA, LET, ROWS, SEQUENCE, INDEX, ROUNDDOWN, and MOD.

= LAMBDA(a,LET(r,ROWS(a),c,COLUMNS(a),idx,SEQUENCE(r*c),INDEX(a,ROUNDDOWN((idx-1)/c,0)+1,MOD(idx-1,c)+1)))

Press the Ctrl + F3 keyboard shortcut to open the Name Manager. Add a new name to a name box. As an optional step, enter a short, descriptive comment. Finally, locate the ‘Refers to:‘ section and paste the LAMBDA formula. Click OK to save the new Excel-compatible FLATTEN function.

add-custom-function

Download the practice Workbook that contains the new function.

We hope you will be familiar with unique and distinct values from now. We will eliminate all LAMBDA formulas and replace them with powerful user-defined functions. Stay tuned.

Related Formulas: