sumifs more than one criteria in same column

[ExcelNoob222]

Hi, I am assuming what I am trying to do is impossible but I figured I'd ask and see if any one has an idea!

Lets say I have the below data

Item	Sales	Move To	% To Move	Moved Amount	Total Sales
1	100				200
2	200	1	50%	100
3	300

The amount in "Total Sales" column is calculated by sales+sumifs($E$2:$E$4,$C$2:$C$4,A2). This works great. But my question is what happens if I have this:

Item	Sales	Move To	% To Move	Moved Amount	Total Sales
1	100				200
2	200	1,3	50%	100
3	300				400

IIs there any way I can get a sumifs or other formula to recognize that column C has more than one value (values will always be separated by a "," if that helps) and therefore assigns 50% of b3 to both a2 and a4?

Also: The "Move To" column must remain as one. I cannot create more columns and do multiple sumifs strung together unfortunately!

Thanks for any help!

 

[Fluff]

Are the item numbers actually 1,2,3 etc & if so what is the highest number you will get?

 

[ExcelNoob222]

Are the item numbers actually 1,2,3 etc & if so what is the highest number you will get?

Unfortunately not, they can be anywhere from 4 to 7 characters in length. Mix of numbers and letters.

 

[Fluff]

In that case try

=B2+SUMPRODUCT((ISNUMBER(FIND(","&A2&",",","&$C$2:$C$4&",")))*($E$2:$E$4))