Add Numbers Associated with Codes From String

[mgirvin]

Dear Smartest Excelers In The World,

In the following 4 cells I have these four text strings:
A2 = PCP, TP, N, N, PA
A3 = N, N, PA
A4 = PCP, N, PA
A5 = TP, N, TP

In A8:B11 I have this table with codes in column A and associated values in column B:
PCP 2
TP 4
N 2
PA 12

In cell B2 I would like a formula that looks at A2 and adds the appropriate values for each code. The formula in cell B2 would: 1) look at A2 and see PCP, TP, N, N, PA; then 2) assign the codes 2, 4, 2, 2, 12; then 3) add 2+4+2+2+12 = 22.

Then I would like to copy the formula to the range B2:B5.

The current method I am using is this: 1) Text To Column; 2) then a table with a COUNTIF and VLOOKUP formula to add for each code; 3) then the SUM function for adding each row.

I am just thinking that one of you Excel Formula Magicians can help me find one formula that would do it.

Any ideas?

[DonkeyOte]

One possible approach:

Sheet1

	A	B
2	PCP, TP, N, N, PA	22
3	N, N, PA	16
4	PCP, N, PA	16
5	TP, N, TP	10
6
7
8	PCP	2
9	TP	4
10	N	2
11	PA	12

Spreadsheet Formulas

Cell Formula B2 =SUMPRODUCT(((LEN(A2)-LEN(SUBSTITUTE(A2,$A$8:$A$11,"")))/LEN($A$8:$A$11)),$B$8:$B$11)

Excel tables to the web >> Excel Jeanie HTML 4

[DonkeyOte]

In the Long Run pending the setup of your lookup values you may find the below more robust:

B2:
=SUMPRODUCT(((LEN($A2&",")-LEN(SUBSTITUTE($A2&",",$A$8:$A$11&",","")))/LEN($A$8:$A$11&",")),$B$8:$B$11)

(such that if you had PC & PCP as 2 separate values in your lookup table the two would be differentiated from one another).

[mgirvin]

Dear DonkeyOte,

That is so clever! Especially the array in the second argument of SUBSTITUTE. I would not have thought to do that. That is one I can add to my Excel Tool Box.

Thanks so much, Formula Magician!