Max, Lookup, Sumproduct Complexity!!

[Bkisley]

Hey everyone! I can not figure this out right now to save my life...so I need help


I need an equation that does the following....


"Take Rate" Tab
cells BM7:BV17 represent how many of the part called out in cell B7 is in each of the full products shown in cells BM6:BV6


"Volume Summary" Tab
cells C60:Q70 represents the total volume of each full product by each year shown in cells C59:Q59

"Allocations" Tab
I need to be able to calculate how many parts are per year since some parts will be on multiple full products
This equation I am after will go on this tab

Example...
Part 123 is on full product A, B and C. I need to know for each year, how many part 123 there are

"Take Rate" tab
cell B7 = part 123
cell BM7 = 1
cell BN7 = 2
cell BO7 = 3

"Volume Summary" Tab
Cell C60 = 10 C61 = 20 C62 =30

"Allocations" Tab
equation needed --> (1*10)+(2*20)+(3*30) = 140
This tells me that 140 part 123's are needed in year one. I will need to do this for every year and for every component

 

[dave2018]

im getting a bit confused... can u upload your workbook, or part of it thats relevant, and show us where and what you want to calculate.

 

[Bkisley]

im getting a bit confused... can u upload your workbook, or part of it thats relevant, and show us where and what you want to calculate.

Unfortunately this is a private document with internal only cost information that I can not share


On my Allocations tab I need a formula that look at horizontal data in cells BM7:BV107 on the "take rate" tab and multiplies it by vertical data in cells C60:Q70 on the "volume summary" tab

Maybe me simplifying it to that level will help?

 

[dave2018]

still don't get it, maybe make a sample sheet with fake data...

 

[Eric W]

OK, Take Rate sheet like this:

	B	C	BM	BN	BO	BP	BQ	BR	BS	BT	BU	BV
5		Products
6	Part		A	B	C	D	E	F	G	H	I	J
7	123		1	2	3
8	456				4	1			7
9	789
10	111		1		2		3		4		5
11	222
12

<tbody>
</tbody>

Take Rate​

Volume Summary sheet like this:

	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
59		2000	2001	2002	2003	2004	2005	2006	2007	2008	2009	2010	2011	2012	2013	2014
60	A	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24
61	B	20	21	31
62	C	30	22	32
63	D
64	E
65	F
66	G
67	H
68	I
69	J

<tbody>
</tbody>

Volume Summary​

Allocations sheet like this:

	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
3		2000	2001	2002	2003	2004	2005	2006	2007	2008	2009	2010	2011	2012	2013	2014
4	123	140	119	170	13	14	15	16	17	18	19	20	21	22	23	24
5	456	120	88	128	0	0	0	0	0	0	0	0	0	0	0	0
6	789	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
7	111	70	55	76	13	14	15	16	17	18	19	20	21	22	23	24
8	222	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0

<tbody>
</tbody>

Allocations​

Worksheet Formulas Cell Formula C4 =MMULT('Take Rate'!$BM7:$BV7+0,'Volume Summary'!C$60:C$69+0) <tbody> </tbody>

<tbody>
</tbody>

Put the formula in C4 and copy down and across as needed. Fairly short formula, but there are a few caveats. The part list on both the Take Rate sheet and the Allocations sheet must be in the same order. Also, the Product list on the Take Rate sheet and on the Volume Summary sheet must be the same size and order, just transposed.

Let us know if this works for you.

 

[Bkisley]

you my friend are a genius!!! Thank you so much

 

[Eric W]

Aw, shucks! I'm happy it works for you!

 

[dave2018]

OK, Take Rate sheet like this:

	B	C	BM	BN	BO	BP	BQ	BR	BS	BT	BU	BV
5		Products
6	Part		A	B	C	D	E	F	G	H	I	J
7	123		1	2	3
8	456				4	1			7
9	789
10	111		1		2		3		4		5
11	222
12

<tbody>
</tbody>

Take Rate​

Volume Summary sheet like this:

	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
59		2000	2001	2002	2003	2004	2005	2006	2007	2008	2009	2010	2011	2012	2013	2014
60	A	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24
61	B	20	21	31
62	C	30	22	32
63	D
64	E
65	F
66	G
67	H
68	I
69	J

<tbody>
</tbody>

Volume Summary​

Allocations sheet like this:

	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
3		2000	2001	2002	2003	2004	2005	2006	2007	2008	2009	2010	2011	2012	2013	2014
4	123	140	119	170	13	14	15	16	17	18	19	20	21	22	23	24
5	456	120	88	128	0	0	0	0	0	0	0	0	0	0	0	0
6	789	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
7	111	70	55	76	13	14	15	16	17	18	19	20	21	22	23	24
8	222	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0

<tbody>
</tbody>

Allocations​

Worksheet Formulas Cell Formula C4 =MMULT('Take Rate'!$BM7:$BV7+0,'Volume Summary'!C$60:C$69+0) <tbody> </tbody>

<tbody>
</tbody>

Put the formula in C4 and copy down and across as needed. Fairly short formula, but there are a few caveats. The part list on both the Take Rate sheet and the Allocations sheet must be in the same order. Also, the Product list on the Take Rate sheet and on the Volume Summary sheet must be the same size and order, just transposed.

Let us know if this works for you.

Wow! i couldn't figure out what he was asking.. pretty cool!