Kit breakdown - how many eaches are sold each week

[B5rocksass]

Hello. I have a list of items the name of which corresponds to multiple other items since they are in a kit. So for example, if you have 5 KITS, you really have 4 of this item, 5 of this item and 3 of this item. I have each step broken down in an ENORMOUS worksheet and there has to be an easier way. There's a grid that shows the kit breakdown number. Another grid that lists how many kits i have, and yet another grid that multiplies them together. For example, if I'm selling Walking Kits for Different Types of Pets:

[TABLE="width: 419"]
<tbody>[TR]
[TD]KIT NAME[/TD]
[TD]CHAIN[/TD]
[TD]COLLAR[/TD]
[TD]TREATS[/TD]
[TD]MANUAL[/TD]
[/TR]
[TR]
[TD]DOGGIEBAG[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]KITTYBAG[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PIGGYBAG[/TD]
[TD][/TD]
[TD][/TD]
[TD]4[/TD]
[TD]4[/TD]
[/TR]
</tbody><colgroup><col><col span="4"></colgroup>[/TABLE]

On the first month of sales I sold the following

[TABLE="width: 435"]
<tbody>[TR]
[TD][/TD]
[TD]Jan 13-14, 2017[/TD]
[TD]Jan 15-21, 2017[/TD]
[TD]Jan 22-28, 2017[/TD]
[TD]Jan 29 - Feb 4, 2017[/TD]
[/TR]
[TR]
[TD]DOGGIEBAG[/TD]
[TD] 5[/TD]
[TD]10[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]KITTYBAG[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]PIGGYBAG[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]3[/TD]
[/TR]
</tbody><colgroup><col><col span="3"><col></colgroup>[/TABLE]

My grid then shows the wk sales for the individual items (I think I have this right - its just an example)
[TABLE="width: 419"]
<tbody>[TR]
[TD][TABLE="width: 419"]
<tbody>[TR]
[TD]Jan 13-14, 2017[/TD]
[TD]CHAIN[/TD]
[TD]COLLAR[/TD]
[TD]TREATS[/TD]
[TD]MANUAL[/TD]
[/TR]
[TR]
[TD]DOGGIEBAG[/TD]
[TD]15[/TD]
[TD]15[/TD]
[TD]15[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]KITTYBAG[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]PIGGYBAG[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]TOTAL[/TD]
[TD]15[/TD]
[TD]15[/TD]
[TD]15[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jan 15-21, 2017[/TD]
[TD]CHAIN[/TD]
[TD]COLLAR[/TD]
[TD]TREATS[/TD]
[TD]MANUAL[/TD]
[/TR]
[TR]
[TD]DOGGIEBAG[/TD]
[TD]30[/TD]
[TD]30[/TD]
[TD]30[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]KITTYBAG[/TD]
[TD]8[/TD]
[TD]8[/TD]
[TD]8[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]PIGGYBAG[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]TOTAL[/TD]
[TD]38[/TD]
[TD]38[/TD]
[TD]38[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jan 22-28, 2017[/TD]
[TD]CHAIN[/TD]
[TD]COLLAR[/TD]
[TD]TREATS[/TD]
[TD]MANUAL[/TD]
[/TR]
[TR]
[TD]DOGGIEBAG[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]KITTYBAG[/TD]
[TD]8[/TD]
[TD]8[/TD]
[TD]8[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]PIGGYBAG[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]8[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]TOTAL[/TD]
[TD]8[/TD]
[TD]8[/TD]
[TD]16[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jan 29 - Feb 4, 2017[/TD]
[TD]CHAIN[/TD]
[TD]COLLAR[/TD]
[TD]TREATS[/TD]
[TD]MANUAL[/TD]
[/TR]
[TR]
[TD]DOGGIEBAG[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]KITTYBAG[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]PIGGYBAG[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]12[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]TOTAL[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]16[/TD]
[TD]12[/TD]
[/TR]
</tbody><colgroup><col><col span="4"></colgroup>[/TABLE]
[/TD]
[/TR]
</tbody><colgroup><col><col span="4"></colgroup>[/TABLE]

Then I move the totals over to another grid and add them:
[TABLE="width: 435"]
<tbody>[TR]
[TD]ITEM[/TD]
[TD]Jan 13-14, 2017[/TD]
[TD]Jan 15-21, 2017[/TD]
[TD]Jan 22-28, 2017[/TD]
[TD]Jan 29 - Feb 4, 2017[/TD]
[/TR]
[TR]
[TD]CHAIN[/TD]
[TD]15[/TD]
[TD]38[/TD]
[TD]8[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]COLLAR[/TD]
[TD]15[/TD]
[TD]38[/TD]
[TD]8[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]TREATS[/TD]
[TD]15[/TD]
[TD]38[/TD]
[TD]16[/TD]
[TD]16[/TD]
[/TR]
[TR]
[TD]MANUAL[/TD]
[TD]15[/TD]
[TD]30[/TD]
[TD]8[/TD]
[TD]12[/TD]
[/TR]
</tbody><colgroup><col><col span="3"><col></colgroup>[/TABLE]

There has got to be an easier way...Please help!

 

[AlanY]

try this for the week summary, then add them all up

Excel 2012
	A	B	C	D	E
1	KIT NAME	CHAIN	COLLAR	TREATS	MANUAL
2	DOGGIEBAG	3	3	3	3
3	KITTYBAG	4	4	4
4	PIGGYBAG			4	4
5
6	On the first month of sales I sold the following
7
8		Jan 13-14, 2017	Jan 15-21, 2017	Jan 22-28, 2017	Jan 29 - Feb 4, 2017
9	DOGGIEBAG	5	10	0	0
10	KITTYBAG	0	2	2	1
11	PIGGYBAG	0	0	2	3
12
13	My grid then shows the wk sales for the individual items (I think I have this right - its just an example)
14	Jan 13-14, 2017	CHAIN	COLLAR	TREATS	MANUAL
15	DOGGIEBAG	15	15	15	15
16	KITTYBAG	0	0	0	0
17	PIGGYBAG	0	0	0	0
18	TOTAL	15	15	15	15
Sheet2

Cell Formulas
Range	Formula
B15	=INDEX($B$9:$E$11,MATCH($A15,$A$9:$A$11,0),MATCH($A$14,$B$8:$E$8,0)) *INDEX($B$2:$E$4,MATCH($A15,$A$2:$A$4,0),MATCH(B$14,$B$1:$E$1,0))

 

[B5rocksass]

Worked wonderfully. Thank you!

 

[AlanY]

you're welcome