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

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.

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.

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

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

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

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

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

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

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

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

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

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

➤ 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