Counting Text Values in Same Cell

[wlugo13]

How can I have Excel 2007 find and count values in the same cell? I am trying to place the same value in the cel and have it counted multiple times.

EX.
SM SA SM Vet Vet E

It results in each value being counted once and I would like them to be counted multiple times

SM: 2
SA: 1
Vet: 2
E: 1

Any help is appreciated.

[Andrew-R]

It's not entirely clear what you're trying to do, but if you want to count how many times a specific sub-string appears then either of these formula should work (with your string in A1):

=(LEN(A1)-LEN(SUBSTITUTE(A1,"SM","")))/LEN("SM")

Or

=SUMPRODUCT(--(MID(A1,ROW(INDIRECT("1:" & LEN(A1))),LEN("SM"))="SM"))

[wlugo13]

@Andrew-R,

"SUMPRODUCT" works for a single cell. I need the results for a cloumn in a table. I used the range, but it didn't transfer the results as expected. I may be reading the formula wrong

(edit)
Would blank cells effect this?

I have list of client families that have the same/ similar identifiers
I'm new to these forums so I'm not really sure how to make a table but I'm going to try to display what I mean...

Family 1: Vet Vet E SM SA SA SA
Family 2: Vet E E SA SM OD
Family 3: Vet E

The values in the same cell and in different rows. Values are duplicated within the family and also used in other families.

Right now with "COUNTIF" it collects the values once per family- Vet: 3/ E: 3/ SM: 2/ SA: 2/ OD: 1
I would like the results to be- Vet: 4/ E:4/ SM: 2/ SA: 4/ OD: 1

[Andrew-R]

Ah, with you ... a variation on the SUBSTITUTE formula should do the job:

=SUMPRODUCT(LEN(A1:A5)-LEN(SUBSTITUTE(A1:A5,"Vet","")))/LEN("Vet")

[wlugo13]

Excellent! I've been working on this and didn't think to use that combination.
I'll be back if something else glitches.