Because we provide 10 values for criteria, we get back an array with 10 results like this: Each number represents a count – “Jim” appears 3 times, “Sue” appears 2 times, and so on. This array is configured as a divisor with 1 as the numerator. After division, we get another array: Any values that occur in just once in the range will appear as 1s, but values that occur multiple times will appear as fractional values that correspond to the multiple. (i.e. a value that appears 4 times in data will generate 4 values = 0.25). Finally, the SUMPRODUCT function sums all values in the array and returns the result.
Handling blank cells
One way to handle blank or empty cells is to adjust the formula as follows: By concatenating an empty string ("") to the data, we prevent zeros from ending up in the array created by COUNTIF when there are blank cells in the data. This is important, because a zero in the divisor will cause the formula to throw a #DIV/0 error. It works because using an empty string ("") for criteria will count empty cells. However, although this version of the formula won’t throw a #DIV/0 error when with blank cells, it will include blank cells in the count. If you want to exclude blank cells from the count, use: This has the effect of canceling out the count of blank cells by making the numerator zero for associated counts.
Slow Performance?
This is a cool and elegant formula, but it calculates much more slowly than formulas that use FREQUENCY to count unique values. For larger data sets, you may want to switch to a formula based on the FREQUENCY function. Here’s a formula for numeric values, and one for text values.
UNIQUE function in Excel 365
In Excel 365, the UNIQUE function provides a better, more elegant way to list unique values and count unique values.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
