How to Count Unique Values in Excel with Multiple Criteria

This article will show you some methods to count unique values in an Excel worksheet based on different criteria. The Excel worksheet can count unique values, especially with multiple criteria. For a dataset, there can be unique values based on different criteria and we often need to count them.

Counting Unique Values in GIF

How to Count Unique Values in Excel with Multiple Criteria: 3 Ways

We have taken this dataset consisting of some great Movies, their Actors, and the Release Years. Let’s see how we can count the unique values based on multiple criteria using this dataset.

Showing Dataset Overview to Count unique Values

Method 1 – Using the COUNTIFS Function to Count Unique Values

We need a criteria table to set criteria.
Allocate 2 cells where you have to insert criteria.
Allocate another cell to insert the Formula.

Allocating Cells for Multiple Criteria

We want to find out the number of movies with Leonardo DiCaprio and released before 2010.
Write Leonardo DiCaprio in F8 and < 2010 in F9.

Inserting COUNTIFS Formula to Count Unique Values

Use the following formula in F5 and press Enter.

=COUNTIFS(C5:C14,F8,D5:D14,F9)

Using COUNTIFS Function

Method 2 – Combining SUM, LEN, UNIQUE, and FILTER Functions

We want to count the number of movies starring Leonardo DiCaprio before the year 2000.

Write 2000 in F9.

Assigning Criteria to Count Unique Values

Use the following formula in F5 and press Enter.

=SUM(--(LEN(UNIQUE(FILTER(B5:B14,(C5:C14=F8)*(D5:D14<F9),"")))>0))

Result of Unique Values with Multiple Criteria

Formula Breakdown

FILTER(B5:B14,(C5:C14=F8)*(D5:D14<F9),””)): The FILTER Function extracts values from B5:B14 within the given criteria (C5:C14=F8) and (D5:D14<F9).
- Output: (Titanic, Romeo + Juliet)
UNIQUE(FILTER(B5:B14,(C5:C14=F8)*(D5:D14<F9),””)): This UNIQUE Function returns the unique values.
- Output: (Titanic, Romeo + Juliet)
LEN(UNIQUE(FILTER(B5:B14,(C5:C14=F8)*(D5:D14<F9),””)))>0): This LEN Function finds the length of each item and checks whether the value is greater than zero or not.
- Output: (1, 1)

SUM(–(LEN(UNIQUE(FILTER(B5:B14,(C5:C14=F8)*(D5:D14<F9),””)))>0)): The SUM Function adds up the result.
- Output: (2)

Method 3 – Merging IFERROR, ROWS, UNIQUE, and FILTER Functions

We want to count the number of movies starred by Leonardo DiCaprio between 1995 and 2010.

Write these criteria in the criteria table.

Assigning Multiple Criteria to Count Unique Values

Use the following formula in F5 and press Enter.

=IFERROR(ROWS(UNIQUE(FILTER(B5:B14, (C5:C14=F8) * (D5:D14>F9)*(D5:D14<F10)))), 0)

We get 3 movies with Leonardo DiCaprio released between 1995 and 2010.

Result of Unique Values with Multiple Criteria

Formula Breakdown

FILTER(B5:B14, (C5:C14=F8) * (D5:D14>F9)*(D5:D14<F10)): This FILTER Function finds values from the data range B5:B14 within the 3 given criteria.
- Output: (Titanic, The Beach, Romeo + Juliet)

UNIQUE(FILTER(B5:B14, (C5:C14=F8) * (D5:D14>F9)*(D5:D14<F10))): The UNIQUE Function gives only the unique values from the data range.
- Output: (Titanic, The Beach, Romeo + Juliet)

ROWS(UNIQUE(FILTER(B5:B14, (C5:C14=F8) * (D5:D14>F9)*(D5:D14<F10)))): The ROWS Function finds and returns the row numbers of a given array.
- Output: (5, 7, 11)

IFERROR(ROWS(UNIQUE(FILTER(B5:B14, (C5:C14=F8) * (D5:D14>F9)*(D5:D14<F10)))), 0): This IFERROR Function returns some specified text in case of an error. Otherwise it returns the result.
- Output: (3)

Counting Unique Values with Single Criteria

We want to find the number of movies with Leonardo DiCaprio.

Write Leonardo DiCaprio in F8.

Assigning Single Criteria for Unique Values

Use the following formula in F5 and press Enter.

=SUM(IF(F8=$C$5:$C$14, 1/(COUNTIFS($C$5:$C$14, F8, $D$5:$D$14, $D$5:$D$14)), 0))

Result for Unique Values with Single Criteria

Formula Breakdown

COUNTIFS($C$5:$C$14, F8, $D$5:$D$14, $D$5:$D$14): This COUNTIF Function counts all the values within given criteria but it doesn’t count the unique value.
- Output: (1,0,1,0,0,0,1,1,0,1)

IF(F8=$C$5:$C$14, 1/(COUNTIFS($C$5:$C$14, F8, $D$5:$D$14, $D$5:$D$14)), 0): This IF Function returns 1 if the criteria is met. Otherwise, it gives 0.
- Output: (1,0,1,0,0,0,1,0,0,1)

SUM(IF(F8=$C$5:$C$14, 1/(COUNTIFS($C$5:$C$14, F8, $D$5:$D$14, $D$5:$D$14)), 0)): This SUM Function adds all the values given in the range.
- Output: (4)