1

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!

user2051347
  • 1,609
  • 4
  • 23
  • 34

1 Answers1

4

Try this one:

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

In picture below unique values highlighted with blue:

enter image description here

Dmitry Pavliv
  • 35,333
  • 13
  • 79
  • 80
  • Thx for your answer! Why are you taking `1* ` in your formula? – user2051347 Mar 07 '14 at 10:06
  • 1
    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))` – Dmitry Pavliv Mar 07 '14 at 10:08