Counting unique values in Excel

The following formula counts the number of unique values in a given cell range

=SUM(IF(FREQUENCY(IF(LEN(B2:B16)>0,MATCH(B2:B16,B2:B16,0),”"),
IF(LEN(B2:B16)>0,MATCH(B2:B16,B2:B16,0),”"))>0,1))

[** Note: replace the B2:B16 range to be whatever your required cell range is]

To calculate, DON’T press ‘Enter’, instead press ‘Ctrl’ + ‘Shift’ + ‘Enter’ and you’re done.

Tah Dah!!

Advertisements

About Cameron
I'm a final year Computer Science/Information Systems major. Already finished my BA in Politics/Philosophy. I do web and software freelance on the side, while I finish studying. Hoping to be self-employed by the end of my degree, otherwise off into the real-world I go....

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: