Count only unique strings

Question

I have a column with strings. I only want to count the strings which are unique.

I know that I could just derive all distinct strings(by pasting them) and then count them with =Count(...). However I do not want to destroy my dataset. Therefore, is there a formula for counting only unique strings?

I really appreciate your answer!

Dmitry Pavliv · Accepted Answer · 2014-03-07 10:01:44Z

4

Try this one:

=SUMPRODUCT(1*(COUNTIF(A1:A10,A1:A10)=1))

In picture below unique values highlighted with blue:

enter image description here

answered Mar 7, 2014 at 10:01

Dmitry Pavliv

35.9k13 gold badges84 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user2051347 Over a year ago

Thx for your answer! Why are you taking 1* in your formula?

Dmitry Pavliv Over a year ago

it's a special trick: COUNTIF(A1:A10,A1:A10)=1 returns true/false array: {FALSE,FALSE,TRUE,FAlSE,...} (sumproduct can't sum this array) and 1*{FALSE,FALSE,TRUE,FAlSE,...} converts this array to number array: {0,0,1,0,...}. Alternatively you could use 1) =SUMPRODUCT(0+(COUNTIF(A1:A10,A1:A10)=1)) 2) =SUMPRODUCT(--(COUNTIF(A1:A10,A1:A10)=1))

Collectives™ on Stack Overflow

Count only unique strings

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related