Sum from a table if the key matches a variable array

[Scotster]

I'm positive there's a way to do this but I can't figure it out and my searching has come up empty so far so hoping someone can help.

I have a reference table with a planned input quantity.

I then have a second table that references the above, I would like to sum the product of hits. Hopefully the examples below indicate what I'm looking to do, I cannot upload on the works PC for security reasons.

Item	Aug	Sept	Oct	Nov	Dec	Jan	Feb
Apples	0	0	0	5	1	10	0
Oranges	3	10	1	1	1	3	5
Bananas	1	1	12	18	5	2	3
Pears	6	15	17	16	26	27	29
Grapes	50	100	100	100	50	50	10

Location	Provides	Aug	Sept	Oct	Nov	Dec	Jan	Feb
Shop1	Apples, Oranges	3	10	1	6
Shop2	Bananas, Pears	7
Shop3	Apples, Oranges, Bananas, Pears	10
Shop4	Bananas, Pears, Grapes	57

Hopefully the above makes sense. The items are variable and at any one time there are around 20, the basic method would be to create 20 columns for each month but that isn't practical. Provides are quite variable as well. Some have only one, others have many.

The locations are in the 1000s and wildly variable, hence why I would really like to get it working as suggested.

Any help appreciated.

 

[Fluff]

For 365, how about

Fluff.xlsm
	A	B	C	D	E	F	G	H
1	Item	Aug	Sept	Oct	Nov	Dec	Jan	Feb
2	Apples	0	0	0	5	1	10	0
3	Oranges	3	10	1	1	1	3	5
4	Bananas	1	1	12	18	5	2	3
5	Pears	6	15	17	16	26	27	29
6	Grapes	50	100	100	100	50	50	10
Sheet1

Fluff.xlsm
	A	B	C	D	E	F	G	H	I
1	Location	Provides	Aug	Sept	Oct	Nov	Dec	Jan	Feb
2	Shop1	Apples, Oranges	3	10	1	6	2	13	5
3	Shop2	Bananas, Pears	7	16	29	34	31	29	32
4	Shop3	Apples, Oranges, Bananas, Pears	10	26	30	40	33	42	37
5	Shop4	Bananas, Pears, Grapes	57	116	129	134	81	79	42
Sheet6

Cell Formulas
Range	Formula
C2:I5	C2	=SUM(SUMIFS(INDEX(Sheet1!$B$2:$H$100,,XMATCH(C$1,Sheet1!$B$1:$H$1)),Sheet1!$A$2:$A$100,TRIM(TEXTSPLIT($B2,","))))

 

[Scotster]

Spot on. Text split was the key that I was looking for. Thankyou so much, especially for getting back so quickly. My head is fried

 

[Fluff]

You're welcome & thanks for the feedback.