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.
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.
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.
- 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.
- 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.
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.
- 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.
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.
- 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.
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.
- 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.
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.
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
Get FREE Advanced Excel Exercises with Solutions!
Thank you so much.. it did helped me.. Love you
Hello Abishek,
You are most welcome.
Regards
ExcelDemy