In a large Excel dataset, it is very much possible to have duplicate values. Anyone often may be required to count unique values such as unique numbers or text. There exist several ways to get unique text and the COUNTIF function is one of them. In this article, I’m going to explain to you how you can use COUNTIF for unique text.

Just to make to explanation easy to understand, I’m going to use a sample dataset of sales information. The dataset contains two columns these are *Sales Person* and *Sales*.

**Table of Contents**Expand

**How to Use COUNTIF for Unique Text: 8 Ways**

**1. Using SUM & COUNTIF Functions to Count Unique Text**

To count **Unique Text** you can use **the SUM function** along with **the COUNTIF function.**

Let me demonstrate to you the process of counting **Unique Text**.

⏩ In cell **E4**, type the following formula.

`=SUM(1/COUNTIF(B4:B13,B4:B13))`

➤ Here, in the **SUM **function, I used **1/COUNTIF(B4:B13,B4:B13)** as **number1**.

➤ Next, in the **COUNTIF **function, I used **B4:B13 **as the **range **and **B4:B13 **as the **criteria**. Then **1 **as a **dividend **to divide the return array (which is the **divisor)** of the **COUNTIF **function.

Now, the **SUM **function will return the total of all numbers in the list.

**Formula Breakdown**

**➦ COUNTIF(B4:B13,B4:B13) —> **It will find out how many times each individual value appears in the specified range.

**Output : **{2;2;1;2;2;2;2;2;2;1}

** ➦ 1/COUNTIF(B4:B13,B4:B13 —>** becomes

**Output : **{0.5;0.5;1;0.5;0.5;0.5;0.5;0.5;0.5;1}

**➦ SUM(1/COUNTIF(B4:B13,B4:B13)) —>** becomes

**Output : **6

⏩ Now, press the **CTRL + SHIFT + ENTER **key and you will get the count of **Unique Text**.

*Remember to press the CTRL + SHIFT + ENTER key because it’s an array formula.*

**2. Using SUMPRODUCT & COUNTIF Functions to Get Distinct Unique Text**

You also can use **the SUMPRODUCT function** and the **COUNTIF **function to get distinct **Unique Text**. It is the easiest and alternate way of **SUM **&** COUNTIF **function.

To begin with,

⏩ In cell **E4**, type the following formula.

`=SUMPRODUCT(1/COUNTIF(B4:B13,B4:B13))`

➤ Here, in the **SUMPRODUCT **function, I used **1/COUNTIF(B4:B13,B4:B13)** as an **array1**.

➤ Next, in the **COUNTIF **function, I used **B4:B13 **as **range **and **B4:B13 **as **criteria**. Then used **1 **as a **dividend **to divide the return value of the **COUNTIF **function.

Now, the **SUMPRODUCT **function will return the total value of all numbers.

⏩ Press the **ENTER **key and you will get the **Unique Text **count.

**3. COUNT Only Unique Text Values Ignoring Numeric and Date Value**

In case you have different types of values in a column and you just want to count the **Unique Text **then you can do it by using **the ISTEXT function** and **the IF function** along with the **SUM **and **COUNTIF** function.

To demonstrate to you the process, I slightly changed my dataset. I inserted a numeric value and date value in the **Sales Person **column.

To start with,

⏩ In cell **E4**, type the following formula.

`=SUM(IF(ISTEXT(B4:B13),1/COUNTIF(B4:B13, B4:B13),""))`

➤ Here, in the **SUM **function, I used **IF(ISTEXT(B4:B13),1/COUNTIF(B4:B13, B4:B13),””)** as **number1**.

➤ Next, in the **IF **function, I used **ISTEXT(B4:B13) **as **logical_test**, **1/COUNTIF(B4:B13, B4:B13) **as **value_if_true **and **“” (Blank) **as **value_if_false**.

In the **ISTEXT **function, I selected the range **B4:B13 **as a **value**. If the cell value is text it will return **TRUE **otherwise **FALSE**.

➤ Then the **IF **function will check the returned value of the **ISTEXT **function. If the value is **TRUE **then it will provide the value otherwise will keep it blank.

➤ In the **COUNTIF **function, I used **B4:B13 **as **range **and **B4:B13 **as **criteria**.

Then used **1 **as a **dividend **to divide the return array (which is the **divisor)** of the **COUNTIF **function.

Now, the **SUM **function will return the total of all numbers in the list.

**Formula Breakdown**

**➦ COUNTIF(B4:B13,B4:B13) —> **It will find out how many times each individual value appears in the specified range.

**Output : **{2;2;1;1;2;2;1;1;1;1}

**➦ 1/COUNTIF(B4:B13,B4:B13 —>** becomes

**Output : **{0.5;0.5;1;1;0.5;0.5;1;1;1;1}

**➦ ISTEXT(B4:B13) —>** becomes

**Output : **{TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE}

**➦ IF(ISTEXT(B4:B13),1/COUNTIF(B4:B13, B4:B13),””) —>** becomes

**Output :** {0.5;0.5;1; ;0.5; 0.5 ;1; ;1;1}

**➦ SUM(IF(ISTEXT(B4:B13),1/COUNTIF(B4:B13, B4:B13),””)) —>** becomes

**Output : **6

⏩ Now, press the **ENTER **key and you will get the count of **Unique Text **while ignoring number and date values.

**4. Count Unique Text Values Ignoring Empty Cells**

While counting **Unique Text **if you have empty cells in a column then it will show an error. To avoid this error, you can use the **SUM **and **COUNTIF **function along with the **IF **function.

To demonstrate to you the procedure, I’m going to use a dataset where I have some empty cells.

⏩ In cell **E4**, type the following formula.

`=SUM(IF(B4:B13<>"",1/COUNTIF(B4:B13, B4:B13), 0))`

➤ Here, in the **SUM **function, I used **IF(B4:B13<>””,1/COUNTIF(B4:B13, B4:B13), 0)** as **number1**.

➤ Next, in the **IF **function, I used **B4:B13<>”” **as **logical_test**, **1/COUNTIF(B4:B13, B4:B13 **as **value_if_true **and **0 **as **value_if_false**. If any cell is empty in the selected range **B4:B13 **then it will return **0 **otherwise it will return the corresponding **COUNTIF **value.

In the **COUNTIF **function, I used **B4:B13 **as **range **and **B4:B13 **as **criteria**.

Then used **1 **as a **dividend **to divide the return array (which is the **divisor)** of the **COUNTIF **function.

Now, the **SUM **function will return the total of all numbers in the list.

⏩ Press the **ENTER **key and you will get the **Unique Text** count while ignoring empty cells.

**5. Get Case-Sensitive Unique Text Using COUNTIF**

Sometimes for a certain dataset, you may need to maintain the case-sensitive issue. If you want or need you can count **Unique Text** while maintaining **case_senstivity**.

Here, I will use the **IF, SUM, **and **the EXACT function** to get the **Unique Text**. Then, I will use the **COUNTIF **function to count the **Unique Text **values.

For the demonstration purpose, I slightly changed some values in the dataset given below.

⏩ In cell **E4**, type the following formula.

`=IF(SUM((--EXACT($B$4:$B$13,B4)))=1,"Unique","Duplicate")`

➤ Here, in the **IF **function, I used **SUM((–EXACT($B$4:$B$13,B4)))=1 **as **logical_test** , **Unique **as **value_if_true **and **Duplicate **as **value_if_false**.

➤ Next, in the **SUM **function, I used **(–EXACT($B$4:$B$13,B4))** as **number1**.

In the **EXACT **function, I selected the range **$B$4:$B$13 **as **text1** and **B4** as **text2**. The **EXACT **function will return a binary value 1 if **text1 **matches with **text2** otherwise **0.**

➤ **(–EXACT($B$4:$B$13,B4))=1 **will convert the binary result into a boolean result **TRUE **or **FALSE**.

➤ Then the **IF **function will check the returned value of the **EXACT **function. If the value is **TRUE, **then it will return **Unique **otherwise **Duplicate**.

⏩ Now, press the **ENTER **key and you will get the exact match result.

⏩ Use** the Fill Handle** to **AutoFill **the formula for the rest of the cells.

To use the **COUNTIF **function,

⏩ In cell **G4**, type the following formula.

`=COUNTIF(E4:E13, "Unique")`

➤ Here, in the **COUNTIF **function, I used **E4:E13 **as **range **and **“Unique” **as **criteria**. Now, it will count all the unique values from the selected range.

⏩ Press the **ENTER **key and you will get the **Unique Text** count.

**6. Combining SUM & COUNTIF Functions to Count Unique Text (Only Occurred Once)**

If you only want the unique texts that occurred only once then you can use the **SUM, IF, ISTEXT, **and the **COUNTIF **function together.

To begin with,

⏩ In cell **E4**, type the following formula.

`=SUM(IF(ISTEXT(B4:B13)*COUNTIF(B4:B13,B4:B13)=1,1,0))`

➤ Here, in the **SUM **function, I used **IF(ISTEXT(B4:B13)*COUNTIF(B4:B13,B4:B13)=1,1,0)** as **number1**.

➤ Next, in the **IF **function, I used **ISTEXT(B4:B13)*COUNTIF(B4:B13,B4:B13)=1 **as **logical_test**, **1 **as **value_if_true **and **0 **as **value_if_false**.

In the **ISTEXT **function, I selected the range **B4:B13 **as a **value**. If the cell value is text, it will return **TRUE **otherwise **FALSE**.

➤ In the **COUNTIF **function, I used **B4:B13 **as **range **and **B4:B13 **as **criteria**.

➤ **ISTEXT(B4:B13)*COUNTIF(B4:B13,B4:B13)=1 **will convert the result into boolean type (**TRUE **or **FALSE**).

Then the **IF **function will check the **logical_test_value**. If the value is **TRUE, **then it will return **1 **otherwise **0**.

Now, the **SUM **function will return the total of all numbers in the list.

⏩ Press the **ENTER **key and you will get only the **Unique Text** that occurred only once.

**7. Using SUMPRODUCT & FREQUENCY Functions to Count Unique Text**

You also can count the **Unique Text** using the **SUMPRODUCT **and** the FREQUENCY function** with **MATCH function** and **ROW function.**

To begin the procedure,

⏩ In cell **E4**, type the following formula.

`=SUMPRODUCT(--(FREQUENCY(MATCH(B4:B13,B4:B13,0),ROW(B4:B13)-ROW(B4)+1)>0))`

➤ Here, in the **SUMPRODUCT **function, I used **–(FREQUENCY(MATCH(B4:B13,B4:B13,0),ROW(B4:B13)-ROW(B4)+1)>0)** as **array1**.

➤ Next, in the **FREQUENCY **function, I used **MATCH(B4:B13,B4:B13,0) **as **data_array** , **ROW(B4:B13)-ROW(B4)+1 **as **bins_array**.

In the **MATCH **function, I selected the range **B4:B13 **as a **lookup_value, B4:B13 **as **lokkup_array, **and **0 **as **match_type** where **0 **means exact match. It will return the position of the first match, values that appear more than once in the data return the same position.

This array will be fed into the **FREQUENCY **function as **data_array**.

➤ In the **ROW **function, I used **B4:B13 **as a **reference**. And **ROW(B4:B13)-ROW(B4)+1 **this part will return a sequential list of numbers for each value in the data and it is fed into the **FREQUENCY **function as **bins_array**.

➤ Now, the **FREQUENCY** function will return an array of numbers that indicate a count for each number in the data array which is organized by bins numbers for each value in the data.

The **SUMPRODUCT **function will check the values that are **greater than 0 **and will convert the result into **TRUE **or **FALSE**. Again, I used **Double Negative **(–) sign to convert the boolean result into a binary number.

Finally, it will return the total of all numbers in the list.

⏩ Press the **ENTER **key and you will get the **Unique Text**.

**8. Using SUM & FREQUENCY Functions to Count Unique Text Based on Criteria**

You also can use the **SUM **function and the **FREQUENCY **function with the **IF **function, the **MATCH **function, and the **ROW **function.

Here, I’ve taken a dataset to count **Unique Text **depending on specific criteria.

To begin the procedure,

⏩ In cell **G4**, type the following formula.

`=SUM(--(FREQUENCY(IF(C4:C13=F4,MATCH(B4:B13,B4:B13,0)),ROW(B4:B13)-ROW(B4)+1)>0))`

➤ Here, in the **SUMPRODUCT **function, I used **–(FREQUENCY(IF(C4:C13=F4,MATCH(B4:B13,B4:B13,0)),ROW(B4:B13)-ROW(B4)+1)>0)** as **array1**.

➤ Next, in the **FREQUENCY **function, I used **IF(C4:C13=F4,MATCH(B4:B13,B4:B13,0)) **as **data_array**, **ROW(B4:B13)-ROW(B4)+1 **as **bins_array**.

After that, in the **IF **function, I used **C4:C13=F4 **as **logical_test** and **MATCH(B4:B13,B4:B13,0) **as **value_if_true**.

➤ Then, in the **MATCH **function, I selected the range **B4:B13 **as a **lookup_value, B4:B13 **as **lokkup_array, **and **0 **as **match_type** where **0 **means exact match. It will return the position of the first match, values that appear more than once in the data return the same position.

➤ Here, the **IF** function acts as a **filter**, it will only allow the values from **MATCH** to pass through if they are related to **“California” **or the value of the** F4** cell. It will return a filtered array which will be fed into the **FREQUENCY **function as **data_array**.

In the **ROW **function, I used **B4:B13 **as a **reference**. And **ROW(B4:B13)-ROW(B4)+1 **this part will return a sequential list of numbers for each value in the data and it is fed into the **FREQUENCY** function as **bins_array**.

➤ Now, the **FREQUENCY** function will return an array of numbers that indicate a count for each number in the data array which is organized by bins numbers for each value in the data.

➤ The **SUM **function will check the values that are **greater than 0 **and will convert the result into **TRUE **or **FALSE**. Again, I used **Double Negative **(–) sign to convert the boolean result into a binary number **(1 **or **0)**.

Finally, it will return the total of all numbers in the list.

*Remember to press the CTRL + SHIFT + ENTER key because it’s an array formula.*

⏩ Press the **CTRL + SHIFT + ENTER **key and you will get the **Unique Text**.

**Things to Remember**

🔺 While using the array formula remember to press **CTRL + SHIFT + ENTER **key.

🔺 You will get the **#DIV/0! **error if you have empty cells in your dataset while using **SUM **or **SUMPRODUCT **with the **COUNTIF **function.

**Practice Section**

I’ve provided a practice sheet in the workbook to practice these explained examples.

**Download to Practice**

**Conclusion**

In this article, I have explained 8 ways to count unique text using **COUNTIF**. I also tried to cover when and why the errors may come frequently while counting unique text. Last but not least, if you have any kind of suggestions, ideas, or feedback please feel free to comment down below.

**<< Go Back to Count | Unique Values | Learn Excel**