How to Count Unique Values in Excel with Multiple Criteria

Get FREE Advanced Excel Exercises with Solutions!

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 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


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)


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: How to Count Unique Text Values with Criteria in Excel


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)


Practice Section

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

Showing Practice section


Download Practice Workbook

You can download this practice workbook.


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.


<< Go Back to Count | Unique Values | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Sourav Kundu
Sourav Kundu

Sourav Kundu, BSc, Naval Architecture & Marine Engineering, Bangladesh University of Engineering and Technology, is a dedicated technical content creator of the ExcelDemy project. He has a keen interest in Excel and he leverages his problem-solving skills to provide solutions on user interface with Excel. In his position as an Excel & VBA Content Developer at ExcelDemy, Sourav Kundu not only adeptly addresses challenging issues but also demonstrates enthusiasm and expertise in navigating complex situations. Apart from creating... Read Full Bio

2 Comments
  1. Reply
    Abhishek Assistant Manager - Data Analyst Bandekar Oct 31, 2023 at 7:54 PM

    Thank you so much.. it did helped me.. Love you

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo