< Go Back

Count unique text values in a range

If you have data that you want to simplify by counting unique text values in a range in excel, SUMPRODUCT and FREQUENCY are of the most useful functions. However, you must incorporate other functions including, MATCH and ROW in the formula for them to work:

General Formula

=SUMPRODUCT(--(FREQUENCY(MATCH(txt,txt,0),ROW(txt)-ROW(txt.firstcell+1)>0))

…..Where”txt” is the data or text in the table range. The

Application of Formula

To count unique text values in different cells with this formula does not take much. The concept and execution are simple. Imagine you are managing a workforce on “Project Y”. From the worksheet, find that same employee appears several times. You can easily count the unique employees or names using the formula. Check out this example:

Figure 1. Example 1 of FREQUENCY Function

From this worksheet, the formula in H9 is:

=SUMPRODUCT(--(FREQUENCY(MATCH(B6:B15, B6:B15,0),ROW(B6:B15)-ROW(B6+1)>0))

Here’s How the Formula Works

This is one is a little bit complex because Frequency does not deal with non-numerical values. That’s why the biggest bit of the works on conversion of non-numeric to numeric so the FREQUENCY function can work on it.

The MATCH is the first function that gets each value/item’s position in the data. The function will also provide the position of the “first match” values appearing twice or more only and that is why only the same number is returned. In this example, MATCH is fed with an array from match_value argument and it will generate this position array, {1;1;1;4;4;6;6;6;9;9). which is then solved by the FREQUENCY.

From this formula, ROW(B6:B15)-ROW(B6)+1, we get another series of data or an array argument. The formula uses row number of every item in the provided data and the number of the row of the  first item to generate this array:

{1;2;3;4;5;6;7;8;9;10}

FREQUENCY will then return array corresponding to bins. This example, the supply is the bins generated by MATCH and that of ROW function as explained above. The function returns an array indicating the count of each value as in the supplied data. FREQUENCY will automatically return zero for numbers appearing more than once in the fed data Here’s the resulting array:

{3;0;0;2;0;3;0;0;2;0;0}

The next thing is the conversion of the array to TRUEs and FALSEs as follows:

{TRUE; FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE}

…this array is then converted to numerical figure that can be worked on by SUMPRODUCT. This is what you get inside SUMPRODUCT:

=SUMPRODUCT {1;0;0;1;0;1;0;0;1;0;0}

=4

Addition, if you have an empty cell in the range, then that would complicate the calculation and this could formula will not work since MATCH will return a #N/A. If you have such a case, you’ll need to add IF to read the empty cells.

Using COUNTIF to Count Unique Values

You can also decide to COUNTIF instead of FREQUENCY to Count unique text values in a range. COUNTIF function is much simpler but not recommended for larger data due to performance issues. For this example, it would do great and still get a 4 as the total count

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar