6
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?

8

Here's a solution:

Worksheet Screenshot

Array enter the following formula anywhere:

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

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".

enter image description here

{=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
3

Here's another solution inspired by Rajesh's own answer:

Worksheet Screenshot

Enter the following formula anywhere:

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.