Sum based on looking up mutliple comma seperated values.

Ronanm

Board Regular
Joined
Nov 13, 2010
Messages
107
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.



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

Some videos you may like

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
Joined
Jun 12, 2014
Messages
38,617
Office Version
365
Platform
Windows
How about
=SUMIF($A$2:$A$5,G1,$B$2:$B$5)
 

Ronanm

Board Regular
Joined
Nov 13, 2010
Messages
107
Apologies... I rushed when sent that out.. It should have been; As there can be repeated values. Sorry

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,617
Office Version
365
Platform
Windows
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
Joined
Nov 13, 2010
Messages
107
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
Joined
Jun 12, 2014
Messages
38,617
Office Version
365
Platform
Windows
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
Joined
Nov 13, 2010
Messages
107
Actually. I need to start again. I change the screenshot half way through...
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,617
Office Version
365
Platform
Windows
Can you please answer my question?
 

Ronanm

Board Regular
Joined
Nov 13, 2010
Messages
107
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.
CodeAmountA123A124A125A126
A123
5​
A124,A126,A124
21​
????
A124
6​
A125
8​
A126
9​
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,617
Office Version
365
Platform
Windows
Have you tried the formula I suggested in post#2?
 

Watch MrExcel Video

Forum statistics

Threads
1,095,480
Messages
5,444,733
Members
405,298
Latest member
fxtrtr17

This Week's Hot Topics

Top