# Sum based on looking up mutliple comma seperated values.

#### Ronanm

##### Board Regular
Hi

In Cell E2 below I have the formula =SUMPRODUCT(SUMIF(\$A\$2:\$A\$5,MID(SUBSTITUTE(D2,",",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(D2,",","")))),4),\$B\$2:\$B\$5)) and it works a treat.
However I need the same in G2 to J2 (the ?'s), where it pulls out the values based on the Heading in G1 to J2. Thanks if you can help.

 Code Amount A123 A124 A125 A126 A123 5​ A124,A126 15​ ? ? ? A124 6​ A125 8​ A126 9​

### Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

#### Fluff

##### MrExcel MVP, Moderator
=SUMIF(\$A\$2:\$A\$5,G1,\$B\$2:\$B\$5)

#### Ronanm

##### Board Regular
Apologies... I rushed when sent that out.. It should have been; As there can be repeated values. Sorry

 Code Amount A123 A124 A125 A126 A123 5​ A124,A126,A124 21​ ? ? ? A124 6​ A125 8​ A126 9​

#### Fluff

##### MrExcel MVP, Moderator
I don't understand, you said you wanted the values based on the headers in G1:J1.
What does the value in D2 have to do with it?

#### Ronanm

##### Board Regular
Sorry. What I should have said was... In E2 I have the formula =SUMPRODUCT(SUMIF(\$A\$2:\$A\$5,MID(SUBSTITUTE(D2,",",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(D2,",","")))),4),\$B\$2:\$B\$5))

#### Fluff

##### MrExcel MVP, Moderator
Your question was about getting the values in G2:J2 based on the values in G1:J1.
So I'll ask again, what does D2 have to do with your question?

#### Ronanm

##### Board Regular
Actually. I need to start again. I change the screenshot half way through...

#### Ronanm

##### Board Regular
In E2 I have the formula =SUMPRODUCT(SUMIF(\$A\$2:\$A\$5,MID(SUBSTITUTE(D2,",",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(D2,",","")))),4),\$B\$2:\$B\$5)).
In D2 I have the values A124,A126,A124

The result in E2 is 21. This is from summing up the Amounts for two A124 and one A126 from Cell D2.
I would like a formula to calculate the totals over in cells G2- J2 (the 's) using the Headers in G1 - J1.
 Code Amount A123 A124 A125 A126 A123 5​ A124,A126,A124 21​ ? ? ? ? A124 6​ A125 8​ A126 9​

#### Fluff

##### MrExcel MVP, Moderator
Have you tried the formula I suggested in post#2?