Sumproduct question (I think)

[Bob White]

Hello Experts

In the attached table SKUs 1,5 and 7 all contain blue.

I want a function that will look up the quantity of each SKU that contains Blue in Table 2 (e.g. SKU 1 =55, SKU 5 =67 and SKU 7 =73) and then multiply and sum each of the quantities by the numbers in the column "Amount 1 per unit" and Amount 2 per unit" in Table 1.

In this example the function will return the 42,263 which equals Blue = SKU 1 (55 x 2x 23) + SKU 5 (67 x 6 x 63) + SKU 7 (8 x 8 x73)

[TABLE="width: 516"]
<tbody>[TR]
[TD="class: xl68, width: 87, bgcolor: transparent"]TABLE 1
[/TD]
[TD="class: xl66, width: 87, bgcolor: transparent"][/TD]
[TD="class: xl66, width: 87, bgcolor: transparent"][/TD]
[TD="class: xl66, width: 87, bgcolor: transparent"][/TD]
[TD="width: 75, bgcolor: transparent"][/TD]
[TD="width: 75, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, width: 87, bgcolor: transparent"]SKU
[/TD]
[TD="class: xl67, width: 87, bgcolor: transparent"]Colour
[/TD]
[TD="class: xl67, width: 87, bgcolor: transparent"]Amount 1 per unit
[/TD]
[TD="class: xl67, width: 87, bgcolor: transparent"]Amount 2 per unit
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]SKU 1
[/TD]
[TD="class: xl65, bgcolor: transparent"]blue
[/TD]
[TD="class: xl65, bgcolor: transparent"]2
[/TD]
[TD="class: xl65, bgcolor: transparent"]23
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]SKU 2
[/TD]
[TD="class: xl65, bgcolor: transparent"]red
[/TD]
[TD="class: xl65, bgcolor: transparent"]3
[/TD]
[TD="class: xl65, bgcolor: transparent"]21
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]SKU 3
[/TD]
[TD="class: xl65, bgcolor: transparent"]green
[/TD]
[TD="class: xl65, bgcolor: transparent"]4
[/TD]
[TD="class: xl65, bgcolor: transparent"]45
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]SKU 4
[/TD]
[TD="class: xl65, bgcolor: transparent"]yellow
[/TD]
[TD="class: xl65, bgcolor: transparent"]5
[/TD]
[TD="class: xl65, bgcolor: transparent"]41
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]SKU 5
[/TD]
[TD="class: xl65, bgcolor: transparent"]blue
[/TD]
[TD="class: xl65, bgcolor: transparent"]6
[/TD]
[TD="class: xl65, bgcolor: transparent"]63
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]SKU 6
[/TD]
[TD="class: xl65, bgcolor: transparent"]red
[/TD]
[TD="class: xl65, bgcolor: transparent"]7
[/TD]
[TD="class: xl65, bgcolor: transparent"]24
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]SKU 7
[/TD]
[TD="class: xl65, bgcolor: transparent"]blue
[/TD]
[TD="class: xl65, bgcolor: transparent"]8
[/TD]
[TD="class: xl65, bgcolor: transparent"]8
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]TABLE 2
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"]SKU
[/TD]
[TD="class: xl70, bgcolor: transparent"]QTY
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"]Colour
[/TD]
[TD="class: xl68, bgcolor: transparent"]TOTAL QTY
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]SKU 1
[/TD]
[TD="class: xl65, bgcolor: transparent"]55
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"]blue
[/TD]
[TD="class: xl69, bgcolor: transparent"]42,263
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]SKU 2
[/TD]
[TD="class: xl65, bgcolor: transparent"]58
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]SKU 3
[/TD]
[TD="class: xl65, bgcolor: transparent"]61
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent, colspan: 6"]Blue = SKU 1 (55 x 2x 23) + SKU 5 (67 x 6 x 63) + SKU 7 (8 x 8 x73)
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]SKU 4
[/TD]
[TD="class: xl65, bgcolor: transparent"]64
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]SKU 5
[/TD]
[TD="class: xl65, bgcolor: transparent"]67
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]SKU 6
[/TD]
[TD="class: xl65, bgcolor: transparent"]70
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]SKU 7
[/TD]
[TD="class: xl65, bgcolor: transparent"]73
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

Shouldn't the result be 32528?

=55*2*23+67*6*63+8*8*73 = 32528

M.

 

[FormR]

Here is one option, note: I get the same result as Marcelo suggests..

Excel 2013/2016
	A	B	C	D	E
1	TABLE 1
2	SKU	Colour	Amount 1 per unit	Amount 2 per unit
3	SKU 1	blue	2	23
4	SKU 2	red	3	21
5	SKU 3	green	4	45
6	SKU 4	yellow	5	41
7	SKU 5	blue	6	63
8	SKU 6	red	7	24
9	SKU 7	blue	8	8
10
11
12	TABLE 2
13	SKU	QTY		Colour	TOTAL QTY
14	SKU 1	55		blue	32528
15	SKU 2	58
16	SKU 3	61
17	SKU 4	64
18	SKU 5	67
19	SKU 6	70
20	SKU 7	73
Sheet1

Cell Formulas
Range	Formula
E14	=SUMPRODUCT(--(B3:B9=D14),C3:C9,D3:D9,SUMIFS(B14:B20,A14:A20,A3:A9))

 

[Marcelo Branco]

If the SKUs are in the same order in both tables you also can try (using data layout provided by FormR)
=SUMPRODUCT(--(B$3:B$9=D14),C$3:C$9,D$3:D$9,B$14:B$20)

M.

 

[Bob White]

Thanks you have been a great help.

What is the purpose of the "-- " after the first bracket in the equation?

 

[Marcelo Branco]

What is the purpose of the "-- " after the first bracket in the equation?

See
https://www.mrexcel.com/forum/excel...ct-two-criterias-post4971369.html#post4971369

M.