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.

**Table of Contents**hide

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

`=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.

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

`=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***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.

`=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***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.

`=COUNTA(UNIQUE(B5:B17,FALSE,TRUE))`

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

`=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 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.