Counting values in a cell

[vallan]

Hi All

I am trying to find out how many unique values in a cell. Take for example,

In
Cell A1, we have BB, CA, DF and in
Cell A2, we have BB, DE, AF and in
Cell A3, we have AF, DF, TT, VA
Cell B2, we have TT, DF, GF, KK, MM

Now I want a count of how many of the unique entries are,

e.g

In another cell, I should have BB=2
In another cell, I should have CA=1
In another cell, I should have AF=2
In another cell, I should have DF=3

and so on.

It easy to do this when there is only one value in a cell but how about if there are more than one value like in the above, how can I separate them and still count them if they occur like this is a spreadsheet?

Thanks

 

[macropod]

Hi vallan,

If you put the string you want to find in cell D1, the following array formula will return the number of occurrences in A1:A4.
=SUM(IF(ISERROR(FIND(D1,A1:A4)),,1))
Note that the test is case-sensitive. If you want both upper & lower case combinations to be counted together, change 'FIND' to 'SEARCH'

Note that, being an array formula, you use Ctrl-Shift-Enter to confirm it, rather than just the enter key.

Cheers

[MS MVP - Word]

 

[vallan]

Thank you so very much and it was really helpful. really worked. I have tried it.

Can it selec for different colours too?

 

[SkipVought]

There is no Excel Spreadsheet function that detects color. You can do a search for VBA solutions.

Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]

 

[vallan]

Thank you