Counting unique entries in a column, not excluding nulls

**Lifeseeker** · 05-30-2013, 05:48 PM

Hi there,

I'm trying to count the # of unique entries in a column, and found this formula, but can't get it to work. Let's just say 10000 is big enough right now.

=SUM(IF(FREQUENCY(IF(LEN(A2:A10000)>0,MATCH(A2:A10000,A2:A10000,0),""), IF(LEN(A2:A10000)>0,MATCH(A2:A10000,A2:A10000,0),""))>0,1))

also attaced the file.

Thanks for your help.

**oeldere** · 05-30-2013, 05:53 PM

It's a matrix formula (and you don't end the formula with CTRL+SHIFT+ENTER) instead of just enter.

The result of the formule is 23.

**Tony Valko** · 05-30-2013, 07:02 PM

The data looks like numbers but it's TEXT.

With the data as is, try this array formula**:

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

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

If you convert the TEXT numbers to numeric numbers, try this normally entered formula:

=SUM(--(FREQUENCY(A2:A10000,A2:A10000)>0))

Quite a difference, eh?

You can convert the TEXT numbers to numeric numbers by doing this...

Select the range A2:A10000
Goto the Data tab>Text to Columns
Click Finish