**General Formula **

**=SUMPRODUCT(1/COUNTIF(data,data))**

**Explanatory Notes**

With the Excel COUNTIF function alongside the SUMPRODUCT function, you can easily count the number of unique values or items within a range of cells in an array or table

**How the Excel Count Unique Values in a Range with COUNTIF Function works**

The Excel COUNTIF function begins work from the background, then to the exterior, the COUNTIF function observes a particular data set and counts the occurrence of individual value within the data. The resulting array of numbers might look similar to this: **{30;30;30;21;21;30;31;31;12;12} or {Joe;Joe;Joe;Kim;Kim;Jeff;Jeff}.**

Once The Excel COUNTIF function is through with the counting of unique values or items, the results are then used to function as a divisor with 1 serving as the numerator. The values within the range that appear once will appear in the data set as 1, but values that appear more than once will be displayed as fractions that match the divisible factor. (e.g., a value that appears 7 times in the range will generate 7 items in the array of values with a value of 1/7 = 0.14).

The SUMPRODUCT function finally comes in to add up all the values within the data set and displays the final result.

**Example 1**

For the example below, use the formula:

**=SUMPRODUCT(1/COUNTIF(B5:B13,B5:B13))**

To count the number of values that are unique to each other

* Figure 1: Example on how to use Counting Unique Values in a Range with COUNTIF function to count values that are unique to each other*

**Example 2**

The formula below

**=SUMPRODUCT(1/COUNTIF(B5:B13,B5:B13))**

Counts the frequency of occurrence for unique values

*Figure 2: Example on how to use Counting Unique Values in a Range with COUNTIF function to count the frequency of unique values*

Managing blank cells with the Excel COUNTIF Function

If your data range contains cells that are blank, all you need do is to modify the formula to an extent as seen below:

**=****SUMPRODUCT****(1/****COUNTIF****(data,data&""))**

Where **data&””** expression stops zeros from being in the created array by the COUNTIF function blank cells exist within the data range. This is done by making sure that the benchmark for counting any blank cell is **“”**, but not a zero (which is a figure). This is necessary to be done because when there is a zero in the divisor, it will flag **#DIV/0** error. On the other hand, this form of this formula below will not flag an error whenever there are empty cells, but it will count the empty cells as well. To avoid such inclusion of empty cells during counts, use the formula below will be helpful:

**=****SUMPRODUCT****((data<>"")/****COUNTIF****(data,data&""))**

**Is it taking lots of time?**

This complexity of this formula makes it calculate at a slow pace than other formulas that utilize FREQUENCY to know the amount of all the unique values. If you are handling a large set of data, you can decide to change to another formula that utilizes the FREQUENCY function for quicker results.

## Leave a Comment