Explanation
Note: Prior to Excel 365, Excel did not have a dedicated function to count unique values. This formula shows one way to count unique values, as long as they are numeric. If you have text values, or a mix of text and numbers, you’ll need to use a more complicated formula .
The Excel FREQUENCY function returns a frequency distribution, which is a summary table that contains the frequency of numeric values, organized in “bins”. We use it here as a roundabout way to count unique numeric values. To apply criteria, we use the IF function.
Working from the inside-out, we first filter values with the IF function:
IF(C5:C14="A",B5:B14) // filter on building A
The result of this operation is an array like this:
{905;905;905;905;773;773;FALSE;FALSE;FALSE;FALSE}
Notice all ids in building B are now FALSE. This array is delivered directly to the FREQUENCY function as the data_array. For the bins_array , we supply the ids themselves:
FREQUENCY({905;905;905;905;773;773;FALSE;FALSE;FALSE;FALSE},{905;905;905;905;773;773;801;963;963;963})
With this configuration, FREQUENCY returns the array below:
{4;0;0;0;2;0;0;0;0;0;0}
The result is a bit cryptic, but the meaning is 905 appears four times, and 773 appears two times. The FALSE values are automatically ignored.
FREQUENCY has a special feature that automatically returns zero for any numbers that have already appeared in the data array, which is why values are zero once a number has been encountered. This is the feature that allows this approach to work.
Next, each of these values is tested to be greater than zero:
{4;0;0;0;2;0;0;0;0;0;0}>0
The result is an array like this:
{TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}
Each TRUE in the list represents a unique number in the list, and we just need to add up the TRUE values with SUM. However, SUM won’t add up logical values in an array, so we need to first coerce the values into 1 or zero. This is done with the double-negative (–). The result is an array of only 1’s or 0’s:
{1;0;0;0;1;0;0;0;0;0;0}
Finally, SUM adds these values up and returns the total, which in this case is 2.
Multiple criteria
You can extend the formula to handle multiple criteria like this:
{=SUM(--(FREQUENCY(IF((criteria1)*(criteria2),values),values)>0))}
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 . These formulas can be adapted to apply logical criteria .
Explanation
This formula is more complicated than a similar formula that uses FREQUENCY to count unique numeric values because FREQUENCY doesn’t normally work with non-numeric values. As a result, a large part of the formula simply transforms the non-numeric data into numeric data that FREQUENCY can handle.
Working from the inside out, the MATCH function is used to get the position of each item that appears in the data:
MATCH(B5:B14,B5:B14,0)
The result from MATCH is an array like this:
{1;1;1;4;4;6;6;6;9;9}
Because MATCH always returns the position of the first match, values that appear more than once in the data return the same position. For example, because “Jim” appears 3 times in the list, he shows up in this array 3 times as the number 1.
This array is fed into FREQUENCY as the data_array argument. The bins_array argument is constructed from this part of the formula:
ROW(B5:B14)-ROW(B5)+1)
which builds a sequential list of numbers for each value in the data:
{1;2;3;4;5;6;7;8;9;10}
At this point, FREQUENCY is configured like this:
FREQUENCY({1;1;1;4;4;6;6;6;9;9},{1;2;3;4;5;6;7;8;9;10})
FREQUENCY returns an array of numbers that indicate a count for each number in the data array, organized by bin. When a number has already been counted, FREQUENCY will return zero. This is a key feature in the operation of this formula. The result from FREQUENCY is an array like this:
{3;0;0;2;0;3;0;0;2;0;0} // output from FREQUENCY
Note: FREQUENCY always returns an array with one more item than the bins_array.
We can now rewrite the formula like this:
=SUMPRODUCT(--({3;0;0;2;0;3;0;0;2;0;0}>0))
Next, we check for values greater than zero (>0), which converts the numbers to TRUE or FALSE, then use a double-negative (–) to convert the TRUE and FALSE values to 1s and 0s. Now we have:
=SUMPRODUCT({1;0;0;1;0;1;0;0;1;0;0})
Finally, SUMPRODUCT simply adds the numbers up and returns the total, which in this case is 4.
Handling blank cells
Empty cells in the range will cause the formula to return an #N/A error. To handle empty cells, you can use a more complicated array formula that uses the IF function to filter out blank values:
{=SUM(IF(FREQUENCY(IF(data<>"", MATCH(data,data,0)),ROW(data)-ROW(data.firstcell)+1),1))}
Note: adding IF makes this into an array formula that requires control-shift-enter.
For more information, see this page .
From Mike Girvin’s excellent book on array formulas, Control-Shift-Enter.
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 . These formulas can be adapted to apply logical criteria .
A pivot table is also an excellent way to list and count unique values .
