how to count multiple values in one cell

[jdelara]

I am working on a spreadsheet that has one cell containing the up to 6 variables ( P,O,S,AN, EN, PN). the cell might contain just one variable (P) or it can be a combination of the other variables. Using a different cell, I need to count the occurrence of each variable.

For example : on cell A1: P,O,S on cell A2: P, O
On the cell BI: I need to get the occurrence of the value "P" from cells A1 and A2. On cell B2: I need to get the occurrence of the value "O" from A1 and A2. Is there a way to do this? Thank you for your help.

[quekbc]

Try this formula.

Row\Col	A	B	C	D
1	P,O,S	P	2	=SUMPRODUCT(--(NOT(ISERROR(FIND(B1,$A$1:$A$3))))) - C6
2	P,O	O	3	=SUMPRODUCT(--(NOT(ISERROR(FIND(B2,$A$1:$A$3)))))
3	PN,O,S	S	2
4		AN	0
5		EN	0
6		PN	1

[jdelara]

Thank you. I'll try the formula and will let you know how it works.

[jdelara]

quekbc .I don't understand the formula you suggested but it worked....THANK YOU!..... .

[FDibbins]

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[jdelara]

Ford,
Thanks for the assist and reminder. Appreciate this site.

[quekbc]

Glad it worked. Thanks for the rep jdelara.