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

This article illustrates 5 methods on how to count unique text values with criteria in excel. You may need to extract customer names who 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.

Download Practice Workbook

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

5 Methods to 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. Use 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.

• You can also use the following formula to count the total unique employee names.

• The COUNTIF Function in the formula returns the frequency of each unique name.

Read More: Count Unique Values with Criteria by SUMPRODUCT in Excel

2. Use UNIQUE Function to Count Unique Text Values 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.

🔎 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

Read More: Excel Formula Count Unique Values (3 Easy Ways)

3. Use 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.

🔎 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

Related Content: How to Use COUNTIF for Unique Text (8 Easiest Ways)

Similar Readings:

• COUNTIFS Unique Values in Excel (3 Easy Ways)
• How to Count Unique Values in Excel Using Pivot Table
• Count Unique Names in Excel (5 Methods)

4. Count 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.

• The COUNTA function in the formula returns the count of unique text values obtained from the UNIQUE function.

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

5. Count 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.

🔎 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 desired text or vales (0) if any error occurs.
Output : 3 

Read More: How to Count Unique Values in Multiple Columns in Excel (5 Ways)

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 your requirement.

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.

Related Articles

• Count Unique Values with Criteria by COUNTIFS in EXCEL (4 Examples)
• Excel VBA: Count Unique Values in a Column (3 Methods)
• How to Count Unique Values in Filtered Column in Excel (5 Methods)