# Count unique value ignoring Text in range

``````123
562
My Data
548
K166
You
1548
123
562
100
My Data
100
``````

I want to count unique values by ignoring text in the above shown list. For this I've tried an array formula, `{=SUM(1/Countif(,,,,))}` but it's not working properly.

Can anyone suggest a formula or even VBA for the task?

Here's a solution: Array enter the following formula anywhere:

``````{=SUM(IFERROR(1/COUNTIF(D1:D12,IFERROR(--D1:D12,)),))}
``````

For the benefit of community I would also like to suggest an array formula with differnt approach, "to count unique values by ignoring text in the given data range". ``````{=SUM(IF(FREQUENCY(A2:A13,A2:A13)>0,1,0))}
``````

Note, finish the formula with Ctrl+Shift+Enter.

• Write your Concern,, Why down voted since been accepted by OP & got six up votes?? – Rajesh S Jun 28 at 4:16

Here's another solution inspired by Rajesh's own answer: Enter the following formula anywhere:

``````=SUM(--(FREQUENCY(D1:D12,D1:D12)>0))
``````

Note that this formula doesn't have to be array entered.