Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Count Unique Values in Excel with Multiple Criteria

In this article, we will show you some methods to count unique values in Excel worksheet based on different criteria. The Excel worksheet can be used to 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


Download Practice Workbook

You can download this practice workbook.


3 Ways to Count Unique Values in Excel with Multiple Criteria

We can use the COUNTIFS function to count unique values with multiple criteria. Also, we will show 2 other methods where we will combine different functions together to count the unique values.

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

Showing Dataset Overview to Count unique Values


1. Using COUNTIFS Function to Count Unique Values

This COUNTIFS Function counts the unique values based on multiple criteria.

  • For this procedure, we need a criteria table to set criteria.
  • So, allocate 2 cells where you have to insert criteria.
  • And allocate another cell to insert the Formula.

Allocating Cells for Multiple Criteria

  • We want to find out the number of movies by Leonardo DiCaprio and released before 2010.
  • First, we have to set the Criteria.
  • So, write Leonardo DiCaprio in F8 and < 2010 in F9.

Inserting COUNTIFS Formula to Count Unique Values

  • Now, Write the following formula in F5 and Press ENTER.

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

Thus, within the given criteria we have got 3 movies that are starred by Leonardo DiCaprio and are released before 2010.

Using COUNTIFS Function

Read More: How to Count Unique Values Based on Criteria in Another Column in Excel


2. Combining SUM, LEN, UNIQUE & FILTER Functions

Now, we will use the combination of SUM, LEN, UNIQUE & FILTER functions to count the unique values with 2 criteria. So, let’s start the procedure.

And, We want to count the number of movies starring Leaonardo DiCaprio before the year 2000.

  • So, write 2000 in F9.

Assigning Criteria to Count Unique Values

  • Now, Write the following formula in F5 and Press ENTER.

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

  • Thereby, we have got 2 movies that starred Leonardo DiCaprio and were released before 2000.

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)

Read More: How to Count Unique Values in Filtered Column in Excel (5 Methods)


Similar Readings


3. Merging IFERROR, ROWS, UNIQUE & FILTER Functions

In this method, we will set 3 criteria and we will merge IFERROR, ROWS, UNIQUE & FILTER together. So, let’s see the procedure.

  • Now, we want to count the number of movies starred by Leonardo DiCaprio between 1995 and 2010.
  • So, Write these criteria in the criteria table.

Assigning Multiple Criteria to Count Unique Values

  • Now, Write the following formula in F5 and Press ENTER.

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

Doing so, we get 3 movies by 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)

Read More: Count Unique Text Values with Criteria in Excel (5 Methods)


Counting Unique Values with Single Criteria

In this section we will show you how you can count unique values using a single criterion.

For that, we have to use SUM, IF and COUNTIF functions together.

  • Now, we want to find the number of movies by Leonardo DiCaprio.
  • So, Write Leonardo DiCaprio in F8.

Assigning Single Criteria for Unique Values

  • Now, Write 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))

Thereby, we have got 4 movies by Leonardo DiCaprio.

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)

Read More: Excel SUMPRODUCT Function to Count Unique Values with Criteria


Practice Section

We have provided this practice section in the practice workbook which you can download and practice yourself.

Showing Practice section


Conclusion

Thank you for reaching this far. We have shown 3 methods to count unique values in Excel with multiple criteria. If there are further queries or suggestions, please mention them in the comment section. For more topics like this you can follow our website ExcelDemy.com.


Related Articles

Sourav Kundu

Sourav Kundu

Bio: Hi, I am Sourav Kundu. I live in Adabor, Dhaka. I graduated in Naval Architecture and Marine Engineering from BUET. I am really excited to be a part of SOFTEKO family. I want to develop my research skill and find innovative solutions for the given problems.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo