0

Given the following file:

Column A---- Column D

alfa ---- (blank)

beta ---- beta

(blank) ---- gamma

(blank) ---- (blank)

omega ---- omega

I want to count the number of unique text cells in the two columns, therefore 4:

alfa, beta, gamma, omega

At the moment I roughly solved by this formula:

=COUNTA(A2:A6)+COUNTA(D2:D6)-SUMPRODUCT(--(A2:A6=D2:D6)*(LEN(A2:A6)>0))

Is there any simpler and more elegant way to do the same thing?

Community
  • 1
  • 1
werty
  • 109
  • 2
  • Your sample references columns A and D, but your formula A and B. – Kyle Aug 05 '15 at 17:07
  • I guess it also depends on whether you want a generic solution which will work for any number of columns, not just two. – XOR LX Aug 05 '15 at 18:01
  • @XORLX It is sufficient for just two columns. – werty Aug 06 '15 at 09:55
  • If the data set were large it might be worthwhile to connect Base or Access to the spreadsheet and use an SQL query. For a smaller dataset maybe check out [this older StackOverflow question](http://stackoverflow.com/questions/18586815/count-unique-values-in-a-column-in-excel) for some ideas. – Lyrl Aug 06 '15 at 15:31

0 Answers0