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 1
SKU	Colour	Amount 1 per unit	Amount 2 per unit
SKU 1	blue	2	23
SKU 2	red	3	21
SKU 3	green	4	45
SKU 4	yellow	5	41
SKU 5	blue	6	63
SKU 6	red	7	24
SKU 7	blue	8	8
TABLE 2
SKU	QTY		Colour	TOTAL QTY
SKU 1	55		blue	42,263
SKU 2	58
SKU 3	61		Blue = SKU 1 (55 x 2x 23) + SKU 5 (67 x 6 x 63) + SKU 7 (8 x 8 x73)
SKU 4	64
SKU 5	67
SKU 6	70
SKU 7	73

<tbody>
</tbody>

 

[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.