Sum based on looking up mutliple comma seperated values.

[Ronanm]

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​

 

[Fluff]

How about
=SUMIF($A$2:$A$5,G1,$B$2:$B$5)

 

[Ronanm]

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]

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]

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]

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]

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

 

[Fluff]

Can you please answer my question?

 

[Ronanm]

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]

Have you tried the formula I suggested in post#2?