I’m using Excel 2010 and have the following formula:

=SUM(--(FREQUENCY(H2:H58, H2:H58)>0))

to count unique ID numbers in H2:H58.  This worked when the numbers were numbers, e.g., 42, 83, 17, 42, 95, 60, 17 (5 unique values).  However I had to change the ID number format to include letters, e.g., "42 CAT", "83 BAT", "17 FOX", "42 DOG".  (That would be 4 unique values, because "42 CAT" ≠ "42 DOG".)  And the formula no longer works, it is returning 0 (instead of the correct count, which, for my real data (not shown), happens to be 43).

The help page for the FREQUENCY function says "FREQUENCY ignores blank cells and text."  So this seems to be the problem; FREQUENCY works only for numbers, and it is treating cells with letters in them as if they were blank.

Can anyone tell me how I can count unique values in a range of text values?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.