Help with formula for unique values - VBA

Hello,

I'm having trouble coming up with a formula that would count unique values in a particular column.
I have, (In column C,) a bunch of values that look similar to this... 050316-22614-03, and I want a tally of unique values.

I can't use a range, ie: C2:C28, because this column is populated from another sheet via a macro and the # of rows will always be different. It needs to count unique values in the whole column, ie. C:C

This is part of a VBA code.

Anyone help?

Cheers,
Richard

here's one way, using an array formula:
=SUM(IF(LEN(A:A)>0,1/COUNTIF(A:A,A:A)))

Array Formulae are a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly braces yourself - it won't work...

What don't you like about it??

here's another array formula:

=SUM(IF(1-ISERROR(A:A),IF(A:A<>"",1))/COUNTIF(A:A,A:A&""))

Are you saying that this is to be incorporated into your VBA? If so, then I'm not sure how anyone helping could have known this at the outset, since you never mentioned it in your OP and this isn't the VBA section of the forum. It might be worth editing your original thread title to reflect this.

=SUMPRODUCT(1/COUNTIF(A:A,A:A&""))

last attempt!!

Originally Posted by rkohman

Thanks Glenn, That seems to be much closer. I've added a -1 to the end to remove the column header... but it is still giving me 1 more than is in the column.

I have 18 unique values, (not including the column header,) and with the -1 it is showing 19.

Any ideas why?

=SUMPRODUCT(1/COUNTIF(A:A,A:A&""))

It's counting 1 instance of "".

Thanks for the fix. I forgot about that!!

You're welcome. Thanks for the feedback!