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

We’ll use a sample dataset of sales information. The dataset contains two columns: Sales Person and Sales. Let’s extract the unique values.

How to Use COUNTIF for Unique Text: 8 Ways

Method 1 – Using SUM and COUNTIF Functions to Count Unique Text

• In cell E4, use the following formula.

We used 1/COUNTIF(B4:B13,B4:B13) as number1. In the COUNTIF function, B4:B13 is the range, and B4:B13 as the criteria. We put 1 as a dividend to divide the return array (which is the divisor) of the COUNTIF function.

The SUM function will return the total of all numbers in the list.

• Press Ctrl + Shift + Enter keys and you will get the count of Unique Text.

Method 2 – Using SUMPRODUCT and COUNTIF Functions to Get the Distinct Unique Text

• In cell E4, use the following formula.

In the SUMPRODUCT function, we used 1/COUNTIF(B4:B13,B4:B13) as array1. In the COUNTIF function, we used the B4:B13 as range and B4:B13 as criteria. The 1 is a dividend to divide the return value of the COUNTIF function.

• Press the Enter key and you will get the Unique Text count.

Method 3 – COUNT Only Unique Text Values Ignoring Numeric and Date Values

We slightly changed my dataset by inserting a numeric value and a date value in the Sales Person column.

• In cell E4, use the following formula.

In the SUM function, we used IF(ISTEXT(B4:B13),1/COUNTIF(B4:B13, B4:B13),””) as number1. In the IF function, we used ISTEXT(B4:B13) as the logical_test, 1/COUNTIF(B4:B13, B4:B13) as value_if_true, and “” (Blank) for value_if_false. In the ISTEXT function, I selected the range B4:B13 as a value.

If the cell contains text, ISTEXT will return TRUE, then the function will provide a value. Otherwise, it returns a blank and goes to the next cell.

In the COUNTIF function, we used B4:B13 as range and B4:B13 as criteria. We used 1 as a dividend to divide the return array of the COUNTIF function. The SUM function will return the total of all numbers in the list.

• Press Enter.

Method 4 – Count Unique Text Values Ignoring Empty Cells

Let’s remove some values from the dataset.

• In cell E4, use the following formula.

In the SUM function, we used IF(B4:B13<>””,1/COUNTIF(B4:B13, B4:B13), 0) as number1.

In the IF function, we 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, then IF will return 0 instead of calculating the result for that cell.

The 1 is used to divide the return array (which is the divisor) of the COUNTIF function. 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.

Method 5 – Get Case-Sensitive Unique Text Using COUNTIF

We slightly changed some values in the dataset to have various spellings in different cases.

• In cell E4, use the following formula.

In the IF function, we used SUM((–EXACT($B$4:$B$13,B4)))=1 as logical_test , Unique as value_if_true and Duplicate as value_if_false.

In the SUM function, we used (–EXACT($B$4:$B$13,B4)) as number1. In the EXACT function, we 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, or 0 otherwise. (–EXACT($B$4:$B$13,B4))=1 will convert the binary result into a boolean 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, the result becomes Duplicate.

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

• In cell G4, use the following formula.

The function will count all the unique values from the selected range.

• Press the Enter key and you will get the Unique Text count.

Method 6 – Combining SUM and COUNTIF Functions to Count Unique Text (Only Occurred Once)

• In cell E4, insert the following formula.

In the SUM function, we used IF(ISTEXT(B4:B13)*COUNTIF(B4:B13,B4:B13)=1,1,0) as number1.

In the IF function, we 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, we selected the range B4:B13 as the value. If the cell value is text, it will return TRUE. Otherwise, it returns FALSE.

In the COUNTIF function, we 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 (TRUE or FALSE). Then, the IF function will check the logical_test_value. If the value is TRUE, it will return 1.

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.

Method 7 – Using SUMPRODUCT and FREQUENCY Functions to Count Unique Text

• In cell E4, use the following formula.

In the SUMPRODUCT function, we used –(FREQUENCY(MATCH(B4:B13,B4:B13,0),ROW(B4:B13)-ROW(B4)+1)>0) as array1.

For the FREQUENCY function, we used MATCH(B4:B13,B4:B13,0) as data_array and ROW(B4:B13)-ROW(B4)+1 as bins_array.

In the MATCH function, we selected the range B4:B13 as the lookup_value, B4:B13 as lookup_array, and 0 as match_type where 0 means exact match. This will return the position of the first match, so 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, we used B4:B13 as a reference. ROW(B4:B13)-ROW(B4)+1 will return a sequential list of numbers for each value in the data and it is fed into the FREQUENCY function as bins_array.

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. We used a Double Negative (–) sign to convert the boolean result into a binary number.

• Press the Enter key to get the result.

Method 8 – Using SUM and FREQUENCY Functions to Count Unique Text Based on Criteria

We’ve put a separate cell as a criterion to filter the table by in the formula.

• In cell G4, use the following formula.

In the SUMPRODUCT function, we used –(FREQUENCY(IF(C4:C13=F4,MATCH(B4:B13,B4:B13,0)),ROW(B4:B13)-ROW(B4)+1)>0) as array1.

In the FREQUENCY function, we used IF(C4:C13=F4,MATCH(B4:B13,B4:B13,0)) as data_array and ROW(B4:B13)-ROW(B4)+1 as bins_array.

For the IF function, we used C4:C13=F4 as logical_test and MATCH(B4:B13,B4:B13,0) as value_if_true.

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

The IF function acts as a filter, and it will only allow the values from MATCH to pass through if the value in the cell in column C is equal to the F4 cell.

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

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. We used the Double Negative (–) sign to convert the boolean result into a binary number (1 or 0).

• Press the Ctrl + Shift + Enter keys to get the result.

Things to Remember

While using the array formula remember to press Ctrl + Shift + Enter key. Excel 365 users can use Enter only.

You will get the #DIV/0! error if you have empty cells unless you use a method to ignore them.

Practice Section

We’ve provided a practice sheet in the workbook so you can experiment.

Download the Practice Workbook

<< Go Back to Learn Excel