How to Count Unique Text Values with Criteria in Excel (5 Methods)

Get FREE Advanced Excel Exercises with Solutions!

This article illustrates 5 methods on how to count unique text values with criteria in Excel. You may need to extract customer names that appear frequently or product names that are sold the most. This may be troublesome for you if you have a large dataset. In that case, this article will help you with your problem. The following picture highlights the purpose of this article.

Count Unique Text Values with Criteria in Excel

Here I am going to highlight 5 methods for you to count unique text values with criteria in Excel. We will use the following dataset to illustrate the methods. The dataset contains a list of employee names along with project names and the number of days each of them worked on those projects. Some of them worked on multiple projects. So let’s start.


1. Using Excel SUMPRODUCT Function to Count Unique Text Values with Criteria

Imagine you want to count the number of projects a certain employee (John) has worked on for more than a certain number of days (5).

  • Then enter the following formula in cell G7. It contains the SUMPRODUCT function to get the desired result.
=SUMPRODUCT((B5:B17=G4)*(D5:D17>G5))

  • You can also use the following formula to count the total unique employee names.
=SUMPRODUCT(1/COUNTIF(B5:B17,B5:B17))
  • The COUNTIF Function in the formula returns the frequency of each unique name.


2. Inserting UNIQUE Function to Count Unique Text Values in Excel with Criteria

Now suppose you want to count the unique names of the employees who have worked on a certain project (A).

  • Then apply the following formula in cell G4. Press CTRL+SHIFT+Enter to apply the formula if you are not using Office365.
=SUM(--(LEN(UNIQUE(FILTER(B5:B17,C5:C17=G4,"")))>0))

UNIQUE Function to Count Unique Text Values with Criteria

🔎 How Does the Formula Work? 

➤ B5:B17
Output : {“John”;”Mark”;”David”;”Luis”;”David”;”Peter”;”Alex”;”Sara”;”John”;”Peter”;”John”;”John”;”David”}

 ➤ (C5:C17=G4)
Output : {TRUE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

 ➤ FILTER(B5:B17,C5:C17=G4,””)
The Filter function returns filtered output.
Output : {“John”;”Mark”;”David”;”Peter”}

 ➤ UNIQUE(FILTER(B5:B17,C5:C17=G4,””))
The UNIQUE function returns unique values from an array.
Output : {“John”;”Mark”;”David”;”Peter”}

 ➤ LEN(UNIQUE(FILTER(B5:B17,C5:C17=G4,””)))
The LEN function returns the length of the outputs from the UNIQUE function.
Output : {4;4;5;5}

 ➤ (LEN(UNIQUE(FILTER(B5:B17,C5:C17=G4,””)))>0)
Output : {TRUE;TRUE;TRUE;TRUE}

 ➤ –(LEN(UNIQUE(FILTER(B5:B17,C5:C17=G4,””)))>0)
Output : {1;1;1;1}

 ➤ SUM(–(LEN(UNIQUE(FILTER(B5:B17,C5:C17=G4,””)))>0))
The SUM function adds up the previous outputs.
Output : 4


3. Applying FREQUENCY Function to Count Unique Text Values with Criteria

You can also use the following formula to get the same result. Use CTRL+SHIFT+Enter to enter the formula if you are not using Office365.

=SUM(--(FREQUENCY(IF(C5:C17=G4,MATCH(B5:B17,B5:B17,0)),ROW(B5:B17)-ROW(B5)+1)>0))

FREQUENCY Function to Count Unique Text Values with Criteria

🔎 How Does the Formula Work?

➤ ROW(B5)
The ROW function returns the row number of cell B5.
Output : {5}

 ➤ ROW(B5:B17)
Output : {5;6;7;8;9;10;11;12;13;14;15;16;17}

 ➤ MATCH(B5:B17,B5:B17,0)
The MATCH function returns the relative position of a value in an array.
Output : {1;2;3;4;3;6;7;8;1;6;1;1;3}

 ➤ C5:C17=G4
Output : {FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE}

 ➤ IF(C5:C17=G4,MATCH(B5:B17,B5:B17,0))
The IF function returns the result if the logical test is true. Otherwise, it returns FALSE.
Output : {FALSE;FALSE;FALSE;4;FALSE;FALSE;FALSE;FALSE;1;6;FALSE;FALSE;FALSE}

 ➤ ROW(B5:B17)-ROW(B5)
Output : {0;1;2;3;4;5;6;7;8;9;10;11;12}

 ➤ FREQUENCY(IF(C5:C17=G4,MATCH(B5:B17,B5:B17,0)),ROW(B5:B17)-ROW(B5)+1)
The FREQUENCY function returns the count of occurrences of values in an array.
Output : {1;0;0;1;0;1;0;0;0;0;0;0;0;0}

 ➤ (FREQUENCY(IF(C5:C17=G4,MATCH(B5:B17,B5:B17,0)),ROW(B5:B17)-ROW(B5)+1)>0)
Output : {TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

 ➤ –(FREQUENCY(IF(C5:C17=G4,MATCH(B5:B17,B5:B17,0)),ROW(B5:B17)-ROW(B5)+1)>0)
Output : {1;0;0;1;0;1;0;0;0;0;0;0;0;0}

 ➤ SUM(–(FREQUENCY(IF(C5:C17=G4,MATCH(B5:B17,B5:B17,0)),ROW(B5:B17)-ROW(B5)+1)>0))
The SUM function adds up the previous outputs.
Output : 3


4. Counting Unique Text Values with Single Criterion

Assume you want to count how many employees have worked on one project only.

  • Then you can apply the simple formula given below. You may need to press CTRL+SHIFT+Enter to apply the formula.
=COUNTA(UNIQUE(B5:B17,FALSE,TRUE))
  • The COUNTA function in the formula returns the count of unique text values obtained from the UNIQUE function.

Count Unique Text Values with Single Criterion


5. Counting Unique Text Values with Multiple Criteria

You can use an alternative formula to count unique text values with multiple criteria. Suppose you want to count the number of employees who have worked on a certain project (B) for at least a certain number of days (5).

  • Then apply the following formula in cell G7 using CTRL+SHIFT+Enter.
=IFERROR(ROWS(UNIQUE(FILTER(B5:B17,(C5:C17=G4)*(D5:D17>=G5)))),0)

Count Unique Text Values with Multiple Criteria

🔎 How Does the Formula Work? 

➤ (D5:D17>=G5)
Output : {TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;TRUE} 

➤ (C5:C17=G4)
Output : {FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE} 

➤ (C5:C17=G4)*(D5:D17>=G5)
Output : {0;0;0;0;0;0;1;1;0;0;1;0;0}

 ➤ B5:B17
Output : {“John”;”Mark”;”David”;”Luis”;”David”;”Peter”;”Alex”;”Sara”;”John”;”Peter”;”John”;”John”;”David”}

 ➤ FILTER(B5:B17,(C5:C17=G4)*(D5:D17>=G5)
The FILTER Function returns the filtered values from the range B5:B17.
Output : {“Alex”;”Sara”;”John”}

 ➤ UNIQUE(FILTER(B5:B17,(C5:C17=G4)*(D5:D17>=G5))
The UNIQUE function returns the unique values from the output of the filter function.
Output : {“Alex”;”Sara”;”John”}

 ➤ ROWS(UNIQUE(FILTER(B5:B17,(C5:C17=G4)*(D5:D17>=G5))))
The ROW function returns the count of rows.
Output : 3

 ➤ IFERROR(ROWS(UNIQUE(FILTER(B5:B17,(C5:C17=G4)*(D5:D17>=G5)))),0)
The IFERROR function returns the desired text or values (0) if any error occurs.
Output : 3 


Things to Remember

  • Press CTRL+SHIFT+Enter to apply the array formulas if you are not using Office365.
  • You can change the criteria in the criteria boxes as per your requirements.

Download Practice Workbook

You can download the practice workbook from the download button below.


Conclusion

Now you know how to count unique text values with criteria in Excel. Please use the comment section below for further queries or suggestions. You can also visit our ExcelDemy blog to read more on Excel. Stay with us and keep learning.


<< 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.
Md. Shamim Reza
Md. Shamim Reza

Md. Shamim Reza, a marine engineer with expertise in Excel and a fervent interest in VBA programming, sees programming as a time-saving tool for data manipulation, file handling, and internet interaction. His diverse skill set encompasses Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning. He holds a B.Sc in Naval Architecture & Marine Engineering from BUET and has transitioned into a content developer role, generating technical content focused on Excel and VBA. Beyond his professional pursuits,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo