Exceljet

Quick, clean, and to the point

Count unique text values with criteria

Excel formula: Count unique text values with criteria
Generic formula 
{=SUM(--(FREQUENCY(IF(criteria,MATCH(vals,vals,0)),ROW(vals)-ROW(vals.first)+1)>0))}
Summary 

To count unique text values in a range with criteria, you can use an array formula based on the FREQUENCY and MATCH functions. In the example shown, the formula in G6 is:

{=SUM(--(FREQUENCY(IF(C5:C11=G5,MATCH(B5:B11,B5:B11,0)),ROW(B5:B11)-ROW(B5)+1)>0))}

which returns 3, since three different people worked on on project Omega.

Note: this is an array formula and must be entered with control + shift + enter.

With Excel 365, you can use a much simpler formula based on the UNIQUE function.
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 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.

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.

Adapted from Mike Givin's excellent book on array formulas, Control-Shift-Enter.
Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.