Product of 2 tables/datasets with multiple criteria (mix of Column/Row)

[Scotster]

Looking for some help with a formula to calculate the total requirement of a part over multiple variants. Example below:

Part list with Type Quantity

	Type1	Type2	Type3	Type4	Type5
Part1	0	1	2	3	4
Part2	4	0	1	2	3
Part3	3	4	0	1	2
Part4	2	3	4	0	1
Part5	1	2	3	4	0

Type demand for each WK

	WK1	WK2	WK3	WK4	WK5
Type1	0	1	1	1	1
Type2	4	4	5	3	6
Type3	12	12	9	9	5
Type4	10	13	13	14	9
Type5	7	6	7	6	7

Total Demand for each part

	WK1	WK2	WK3	WK4	WK5
Part1	86
Part2
Part3
Part4
Part5

86 is worked out from the part1 * type1 + part1 * type2, etc. Fairly certain I should be able to use a sumproduct, but i'm struggling as I'm matching column headings to rows etc.

Any help greatly appreciated

 

[Cubist]

Try:

=MMULT(B2:F6,B9:F13)

where B2:F6 is Part list with Type Quantity
and B9:F13 is Type demand for each WK

 

[Scotster]

Try:

=MMULT(B2:F6,B9:F13)

where B2:F6 is Part list with Type Quantity
and B9:F13 is Type demand for each WK

Ahh, apologies. I just did 5 of each to keep it short... I should have said the part list will be significantly bigger than just 6 parts.

The part1 to part5 list can go on for 1000s of lines and won't ever be a similar size to the Type demand table.

I'll update the tables so that this is easily seen.

 

[Scotster]

Updated table to show dissimilar layout.

Part list with Type Quantity

	Type1	Type2	Type3	Type4	Type5
Part1	0	1	2	3	4
Part2	4	0	1	2	3
Part3	3	4	0	1	2
Part4	2	3	4	0	1
Part5	1	2	3	4	0
Part6	5	4	3	2	1

Type demand for each WK

	WK1	WK2	WK3	WK4	WK5	WK6
Type1	0	1	1	1	1	5
Type2	4	4	5	3	6	2
Type3	12	12	9	9	5	4
Type4	10	13	13	14	9	7
Type5	7	6	7	6	7	9

Total Demand for each part

	WK1	WK2	WK3	WK4	WK5	WK6
Part1	86
Part2
Part3
Part4
Part5
Part6

 

[Cubist]

That shouldn't matter as long as you have same number of Type in both tables.

Book1
	A	B	C	D	E	F
1		Type1	Type2	Type3	Type4	Type5
2	Part1	0	1	2	3	4
3	Part2	4	0	1	2	3
4	Part3	3	4	0	1	2
5	Part4	2	3	4	0	1
6	Part5	1	2	3	4	0
7	Part6	5	4	3	2	1
8
9		WK1	WK2	WK3	WK4	WK5
10	Type1	0	1	1	1	1
11	Type2	4	4	5	3	6
12	Type3	12	12	9	9	5
13	Type4	10	13	13	14	9
14	Type5	7	6	7	6	7
15
16		WK1	WK2	WK3	WK4	WK5
17	Part1	86	91	90	87	71
18	Part2	53	60	60	59	48
19	Part3	40	44	50	41	50
20	Part4	67	68	60	53	47
21	Part5	84	97	90	90	64
22	Part6	79	89	85	78	69
Sheet2

Cell Formulas
Range	Formula
B17:F22	B17	=MMULT(B2:F7,B10:F14)
Dynamic array formulas.

 

[Scotster]

Doh, I hadn't changed the formula to cover the whole table.

That's brilliant, I didn't realise it could be done so easily. Many thanks.

 

[Scotster]

Copied it into the large dataset and it works. The only limitation is the Part list with Qty must be the same size and order as the Total demand part list. I have a workaround where I can build the first table and then re-create the third table each time.

To save with this, is there any way to have the result table give the values based on the part number referenced? For example:

	WK1	WK2	WK3	WK4	WK5
Part2
Part4
Part1

With this I could have the part table fixed with the values known, the second table fixed with the values known and the 3rd table able to update with reduced, and specified, part lists.

 

[Cubist]

Try:

Book1
	A	B	C	D	E	F
1		Type1	Type2	Type3	Type4	Type5
2	Part1	0	1	2	3	4
3	Part2	4	0	1	2	3
4	Part3	3	4	0	1	2
5	Part4	2	3	4	0	1
6	Part5	1	2	3	4	0
7	Part6	5	4	3	2	1
8
9		WK1	WK2	WK3	WK4	WK5
10	Type1	0	1	1	1	1
11	Type2	4	4	5	3	6
12	Type3	12	12	9	9	5
13	Type4	10	13	13	14	9
14	Type5	7	6	7	6	7
15
16		WK1	WK2	WK3	WK4	WK5
17	Part2	53	60	60	59	48
18	Part4	67	68	60	53	47
19	Part1	86	91	90	87	71
Sheet2

Cell Formulas
Range	Formula
B17:F19	B17	=MMULT(FILTER($B$2:$F$7,$A$2:$A$7=A17),$B$10:$F$14)
Dynamic array formulas.

 

[Scotster]

Nailed it, absolutely perfect.

Hadn't ever used MMULT before either, it seems very powerful.

 

[Cubist]

You're welcome.
MMULT is short for matrix multiplication. In simple terms, it's repeated sum products.