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.

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.

**Table of Contents**Expand

## 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))`

**🔎 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))`

**🔎 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**.

## 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)`

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