Counting unique values in column

W

WildWill

I have a column containing text values which could be unique, repetitive or
some rows could be empty. I need a formula that will count the total number
of unique values only, (not counting the empty cells) e.g. in the example
below, the answer will be "3":

Column A
Fish
Fish
(Empty)
Chicken
Rat
Chicken
(Empty)
 
D

Domenic

WildWill said:
I have a column containing text values which could be unique, repetitive or
some rows could be empty. I need a formula that will count the total number
of unique values only, (not counting the empty cells) e.g. in the example
below, the answer will be "3":

Column A
Fish
Fish
(Empty)
Chicken
Rat
Chicken
(Empty)
Click to expand...

Here's another way...

=SUM(IF(FREQUENCY(IF(A2:A100<>"",MATCH("~"&A2:A100,A2:A100&"",0)),ROW(A2:
A100)-ROW(A2)+1),1))

....confirmed with CONTROL+SHIFT+ENTER.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to center adjust text above and image in a two column document. 1
Counting unique values in multiple columns 4
Unique Value Count in 3 Columns Using Search Criteria 0
Count of unique values 8
Counting number of unique datafields in a column 2
Counting unique values 4
Counting Unique Values That Are Separate by Commas in a Column 2
Counting unique values based on 2 columns 3

Top