Explanation
This is a complex formula that uses FREQUENCY to count numeric values that are derived with the MATCH function. Working from the inside out, the MATCH function is used to get the position of each value that appears in the data:
MATCH(B5:B11,B5:B11,0)
The result from MATCH is an array like this:
{1;1;3;1;1;6;7}
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 4 times in the list, he shows up in this array 4 times as the number 1.
Outside of the MATCH function, the IF function is used to apply criteria, which in this case involves testing if the project is “omega” (from cell G5):
IF(C5:C11=G5 // filter on "omega"
The IF function acts like a filter, only allowing the values from MATCH to pass through if they are associated with “omega”. The result is an array like this:
{FALSE;FALSE;FALSE;1;1;6;7} // after filtering
The filtered array is delivered directly to the FREQUENCY function as the data_array argument. Next, the ROW function is used to build a sequential list of numbers for each value in the data:
ROW(B3:B12)-ROW(B3)+1
This creates an array like this:
{1;2;3;4;5;6;7;8;9;10}
which becomes the bins_array argument in FILTER. At this point, we have:
FREQUENCY({FALSE;FALSE;FALSE;1;1;6;7},{1;2;3;4;5;6;7})
FREQUENCY returns an array of numbers that indicate a count for each value in the data array, organized by bin. When a number has already been counted, FREQUENCY will return zero. The result from FREQUENCY is an array like this:
{2;0;0;0;0;1;1;0} // result from FREQUENCY
Note: FREQUENCY always returns an array with one more item than the bins_array .
At this point, we can rewrite the formula like this:
=SUM(--({2;0;0;0;0;1;1;0}>0))
We check for values greater than zero, which converts the numbers to TRUE or FALSE:
=SUM(--({TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE}))
Then we use a double-negative to coerce the logical values to 1s and 0s:
=SUM({1;0;0;0;0;1;1;0})
Finally, the SUM function returns 3 as the final result.
Note: this is an array formula and must be entered using Control + Shift + Enter in Excel 2019 and earlier.
Handling empty cells in the range
If any cells in the range are empty, you’ll need to adjust the formula to prevent empty cells from being passed into the MATCH function, which will throw an error. You can do this by adding another nested IF function to check for blank cells:
{=SUM(--(FREQUENCY(IF(B5:B11<>"",IF(C5:C11=G5,MATCH(B5:B11,B5:B11,0))),ROW(B5:B11)-ROW(B5)+1)>0))}
With two criteria
If you have two criteria, you can extend the logic of the formula by adding another nested IF:
{=SUM(--(FREQUENCY(IF(c1,IF(c2,MATCH(vals,vals,0))),ROW(vals)-ROW(vals.1st)+1)>0))}
Where c1 = criteria1, c2 = criteria2 and vals = the values range.
With boolean logic
With boolean logic , you can reduce nested IFs :
{=SUM(--(FREQUENCY(IF((criteria1)*(criteria2),MATCH(vals,vals,0)),ROW(vals)-ROW(vals.1st)+1)>0))}
This makes it easier to add and manage additional criteria.
Note: I adapted the formulas above 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 .
Explanation
Working from the inside out, COUNTIF is configured to values in the range B5:B14, using all of these same values as criteria:
COUNTIF(B5:B14,B5:B14)
Because we provide 10 values for criteria, we get back an array with 10 results like this:
{3;3;3;2;2;3;3;3;2;2}
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:
{0.333333333333333;0.333333333333333;0.333333333333333;0.5;0.5;0.333333333333333;0.333333333333333;0.333333333333333;0.5;0.5}
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:
=SUMPRODUCT(1/COUNTIF(data,data&""))
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:
=SUMPRODUCT((data<>"")/COUNTIF(data,data&""))
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 .
