function to count unique values

I was looking for a way to count unique values in column which may or may not contain blanks. I kept seeing references to the MOREFUNC Add-In, but I can't download it, so kept searching. At this post (http://www.mrexcel.com/forum/showthr...-CountDiff-VBA) Teemar posted a function which seems to do the trick; See below:

Please Login or Register  to view this content.

It's used like this:

Please Login or Register  to view this content.

The above code counts the unique values in the range A1:A100. My problem is that it counts blanks as a unique value also. So, for my columns that I know might have blanks I use it like this:

Please Login or Register  to view this content.

The problem is that I don't always know when a column has blanks, so I'd like some help in adapting this or a similar function to count only the non-blank unique values in a range. Any help is greatly appreciated.

Steffen, you're too quick! You solved it right off the bat, but I already found another constraint I need. How could we configure it to count only the unique visible cells?

That was the same way I thought about doing it, but it doesn't work. I've attached the output I'm seeing after updating the function and updating the formulas.

You can Use excel array function. In B1
like this =SUM(1/COUNTIF(A1:A5,A1:A5)) hit ctrl+shft+enter

mmmarks,
The method you mention would work, but in this case I've got multiple different columns of values I want the unique counts for, depending on how they're filtered. A function would be much more friendly for this application.

Steffen,
That one also didn't work, and now counts blanks back in with unique values. So, for instance, if there are 10 values in a colum, the returned value is 11. If I filter so only 1 is showing, the returned value is still 11.

I've attached a small sample representing the data I'm working with so you can see the results I'm seeing.

Wahoo! You got it! There's a world of difference between AND and OR, isn't there?

Steffen (and Teemar, whereever you are, or whoever made the function that you posted) THANK YOU, THANK YOU, THANK YOU! This one's going to go into my toolbox; I think I'm going to get a lot of use out of this one.

mmmarks, thanks so much for trying and pointing out a simpler method. Yours was a worthy solution to a more minor problem, and I thank you for it.

Originally Posted by jomili

mmmarks, thanks so much for trying and pointing out a simpler method. Yours was a worthy solution to a more minor problem, and I thank you for it.

You are welcome