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​

 

[Ronanm]

Thanks but the values I need to consider are in Cell D2 as well. So in the case of H2, the "?" That will sum up A124 Codes, as the header in H1 saysA124, and as there are two of them in Cell D2, the total will be 12.
Cheers

 

[bosco_yip]

In G2, formula copied across to J2 :

=IFERROR(1/(1/(VLOOKUP(G$1,$A$2:$B$5,2,0)*((LEN($D2)-LEN(SUBSTITUTE($D2,G$1,"")))/LEN(G$1)))),"")

Regards

 

[Ronanm]

In G2, formula copied across to J2 :

=IFERROR(1/(1/(VLOOKUP(G$1,$A$2:$B$5,2,0)*((LEN($D2)-LEN(SUBSTITUTE($D2,G$1,"")))/LEN(G$1)))),"")

Regards

That is perfect. Thank you so much ?

 

[dha17]

I'm looking for a very similar formula for cell E2 (see below). How would this formula be modified if the "Code" was not always 4 characters (could be any number of characters) and the delimiter in cell D2 is a ", " (comma followed by a space)?

=SUMPRODUCT(SUMIF($A$2:$A$5,MID(SUBSTITUTE(D2,",",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(D2,",","")))),4),$B$2:$B$5))

 

[Fluff]

Please start a new thread for this question.

Also I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)