Product of Index & matches - need help to make more efficient

1

15266

New Member
Joined
Feb 10, 2016
Messages
10
Hi Everyone,

I'm looking for some help to simplify the following:

1) I have 1 sheet with a large table A1:D4 showing sales volume (Mt) per productcode (column A) and per months of June, July and August (row 1)


[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]June[/TD]
[TD]July[/TD]
[TD]August[/TD]
[/TR]
[TR]
[TD]ProductCode1[/TD]
[TD]100[/TD]
[TD]50[/TD]
[TD]200
[/TD]
[/TR]
[TR]
[TD]ProductCode2[/TD]
[TD]150[/TD]
[TD]80[/TD]
[TD]120[/TD]
[/TR]
[TR]
[TD]ProductCode3[/TD]
[TD]200[/TD]
[TD]90[/TD]
[TD]350[/TD]
[/TR]
</tbody>[/TABLE]

2) I have a 2nd sheet with a large table A1:E4 showing the % of each ingredients (row 1) that the productcodes (column A) are composed.


[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Ingredient1[/TD]
[TD]Ingredient2[/TD]
[TD]Ingredient3[/TD]
[TD]Ingredient4[/TD]
[/TR]
[TR]
[TD]ProductCode1[/TD]
[TD]20%
[/TD]
[TD]5%[/TD]
[TD]0%[/TD]
[TD]75%[/TD]
[/TR]
[TR]
[TD]ProductCode2[/TD]
[TD]80%[/TD]
[TD]20%[/TD]
[TD]0%[/TD]
[TD]0%[/TD]
[/TR]
[TR]
[TD]ProductCode3[/TD]
[TD]10%[/TD]
[TD]20%[/TD]
[TD]30%[/TD]
[TD]40%[/TD]
[/TR]
</tbody>[/TABLE]


Goal: I'm looking to summarize in 1 (total) cell for each month, the volume (Mt) of the ingredients needed.

So far I managed only to work out ingredient per ingredient, month per month by pulling down -and right- something like the following formula.
Unfortunately this takes up more space than the original tables themselves.

INDEX(Sheet2!B2:E4;MATCH("ProductCode1";Sheet2!A2:A4;0);MATCH("Ingredient1";Sheet2!B1:E1;0))*INDEX(Sheet1!B2:D4;MATCH("June";Sheet1!B1:D1;0))

Would anyone have a better solution, that would allow me to summarize the ingredient volume (Mt) in 1 cell per month?
Thanks in advance for anyone willing to help out!
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Sort by date Sort by votes
Maybe something like this

Sheet2

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]
A​
[/td][td="bgcolor: #DCE6F1"]
B​
[/td][td="bgcolor: #DCE6F1"]
C​
[/td][td="bgcolor: #DCE6F1"]
D​
[/td][td="bgcolor: #DCE6F1"]
E​
[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]
1​
[/td][td][/td][td]
Ingredient1​
[/td][td]
Ingredient2​
[/td][td]
Ingredient3​
[/td][td]
Ingredient4​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
2​
[/td][td]
ProductCode1​
[/td][td]
20%​
[/td][td]
5%​
[/td][td]
0%​
[/td][td]
75%​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
3​
[/td][td]
ProductCode2​
[/td][td]
80%​
[/td][td]
20%​
[/td][td]
0%​
[/td][td]
0%​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
4​
[/td][td]
ProductCode3​
[/td][td]
10%​
[/td][td]
20%​
[/td][td]
30%​
[/td][td]
40%​
[/td][/tr]
[/table]

Sheet1
I reversed the order of the products in column A to make the test more real.

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]
A​
[/td][td="bgcolor: #DCE6F1"]
B​
[/td][td="bgcolor: #DCE6F1"]
C​
[/td][td="bgcolor: #DCE6F1"]
D​
[/td][td="bgcolor: #DCE6F1"]
E​
[/td][td="bgcolor: #DCE6F1"]
F​
[/td][td="bgcolor: #DCE6F1"]
G​
[/td][td="bgcolor: #DCE6F1"]
H​
[/td][td="bgcolor: #DCE6F1"]
I​
[/td][td="bgcolor: #DCE6F1"]
J​
[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]
1​
[/td][td][/td][td]
June​
[/td][td]
July​
[/td][td]
August​
[/td][td][/td][td][/td][td]
Ingredient1​
[/td][td]
Ingredient2​
[/td][td]
Ingredient3​
[/td][td]
Ingredient4​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
2​
[/td][td]
ProductCode3​
[/td][td]
200​
[/td][td]
90​
[/td][td]
350​
[/td][td][/td][td]
June​
[/td][td="bgcolor:#D9D9D9"]
160​
[/td][td="bgcolor:#D9D9D9"]
75​
[/td][td="bgcolor:#D9D9D9"]
60​
[/td][td="bgcolor:#D9D9D9"]
155​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
3​
[/td][td]
ProductCode2​
[/td][td]
150​
[/td][td]
80​
[/td][td]
120​
[/td][td][/td][td]
July​
[/td][td="bgcolor:#D9D9D9"]
83​
[/td][td="bgcolor:#D9D9D9"]
36,5​
[/td][td="bgcolor:#D9D9D9"]
27​
[/td][td="bgcolor:#D9D9D9"]
73,5​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
4​
[/td][td]
ProductCode1​
[/td][td]
100​
[/td][td]
50​
[/td][td]
200​
[/td][td][/td][td]
August​
[/td][td="bgcolor:#D9D9D9"]
171​
[/td][td="bgcolor:#D9D9D9"]
104​
[/td][td="bgcolor:#D9D9D9"]
105​
[/td][td="bgcolor:#D9D9D9"]
290​
[/td][/tr]
[/table]

Array formula in G2 copied across and down
=SUMPRODUCT(INDEX($B$2:$D$4,0,MATCH($F2,$B$1:$D$1,0)),INDEX(INDEX(Sheet2!$B$2:$E$4,0,MATCH(G$1,Sheet2!$B$1:$E$1,0)),N(IF(1,MATCH($A$2:$A$4,Sheet2!$A$2:$A$4,0)))))
confirmed with Ctrl+Shift+Enter, not just Enter

Hope this helps

M.
 
Upvote 0
Marcelo Branco said:
Maybe something like this

Sheet2

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]
A​
[/TD]
[TD="bgcolor: #DCE6F1"]
B​
[/TD]
[TD="bgcolor: #DCE6F1"]
C​
[/TD]
[TD="bgcolor: #DCE6F1"]
D​
[/TD]
[TD="bgcolor: #DCE6F1"]
E​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
1​
[/TD]
[TD][/TD]
[TD]
Ingredient1​
[/TD]
[TD]
Ingredient2​
[/TD]
[TD]
Ingredient3​
[/TD]
[TD]
Ingredient4​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
2​
[/TD]
[TD]
ProductCode1​
[/TD]
[TD]
20%​
[/TD]
[TD]
5%​
[/TD]
[TD]
0%​
[/TD]
[TD]
75%​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
3​
[/TD]
[TD]
ProductCode2​
[/TD]
[TD]
80%​
[/TD]
[TD]
20%​
[/TD]
[TD]
0%​
[/TD]
[TD]
0%​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
4​
[/TD]
[TD]
ProductCode3​
[/TD]
[TD]
10%​
[/TD]
[TD]
20%​
[/TD]
[TD]
30%​
[/TD]
[TD]
40%​
[/TD]
[/TR]
</tbody>[/TABLE]


Sheet1
I reversed the order of the products in column A to make the test more real.

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]
A​
[/TD]
[TD="bgcolor: #DCE6F1"]
B​
[/TD]
[TD="bgcolor: #DCE6F1"]
C​
[/TD]
[TD="bgcolor: #DCE6F1"]
D​
[/TD]
[TD="bgcolor: #DCE6F1"]
E​
[/TD]
[TD="bgcolor: #DCE6F1"]
F​
[/TD]
[TD="bgcolor: #DCE6F1"]
G​
[/TD]
[TD="bgcolor: #DCE6F1"]
H​
[/TD]
[TD="bgcolor: #DCE6F1"]
I​
[/TD]
[TD="bgcolor: #DCE6F1"]
J​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
1​
[/TD]
[TD][/TD]
[TD]
June​
[/TD]
[TD]
July​
[/TD]
[TD]
August​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
Ingredient1​
[/TD]
[TD]
Ingredient2​
[/TD]
[TD]
Ingredient3​
[/TD]
[TD]
Ingredient4​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
2​
[/TD]
[TD]
ProductCode3​
[/TD]
[TD]
200​
[/TD]
[TD]
90​
[/TD]
[TD]
350​
[/TD]
[TD][/TD]
[TD]
June​
[/TD]
[TD="bgcolor: #D9D9D9"]
160​
[/TD]
[TD="bgcolor: #D9D9D9"]
75​
[/TD]
[TD="bgcolor: #D9D9D9"]
60​
[/TD]
[TD="bgcolor: #D9D9D9"]
155​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
3​
[/TD]
[TD]
ProductCode2​
[/TD]
[TD]
150​
[/TD]
[TD]
80​
[/TD]
[TD]
120​
[/TD]
[TD][/TD]
[TD]
July​
[/TD]
[TD="bgcolor: #D9D9D9"]
83​
[/TD]
[TD="bgcolor: #D9D9D9"]
36,5​
[/TD]
[TD="bgcolor: #D9D9D9"]
27​
[/TD]
[TD="bgcolor: #D9D9D9"]
73,5​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
4​
[/TD]
[TD]
ProductCode1​
[/TD]
[TD]
100​
[/TD]
[TD]
50​
[/TD]
[TD]
200​
[/TD]
[TD][/TD]
[TD]
August​
[/TD]
[TD="bgcolor: #D9D9D9"]
171​
[/TD]
[TD="bgcolor: #D9D9D9"]
104​
[/TD]
[TD="bgcolor: #D9D9D9"]
105​
[/TD]
[TD="bgcolor: #D9D9D9"]
290​
[/TD]
[/TR]
</tbody>[/TABLE]


Array formula in G2 copied across and down
=SUMPRODUCT(INDEX($B$2:$D$4,0,MATCH($F2,$B$1:$D$1,0)),INDEX(INDEX(Sheet2!$B$2:$E$4,0,MATCH(G$1,Sheet2!$B$1:$E$1,0)),N(IF(1,MATCH($A$2:$A$4,Sheet2!$A$2:$A$4,0)))))
confirmed with Ctrl+Shift+Enter, not just Enter

Hope this helps

M.
Click to expand...
Fantastic! Could attach and if(and( statement before it with 2 criteria? For example these products might be produced on different lines, so for the whole range in sheet 1 I would want to if(and(E:E="line1"; D:D="colour black"); then followed by the array?
 
Upvote 0
15266 said:
Fantastic! Could attach and if(and( statement before it with 2 criteria? For example these products might be produced on different lines, so for the whole range in sheet 1 I would want to if(and(E:E="line1"; D:D="colour black"); then followed by the array?
Click to expand...

This should complicate the formula. Could you provide an example along with the expected results?

By the way, we can simplify the original formula with a different configuration and as long as the product codes in Sheet2 column A are in ascending order.
Something like,

Sheet1

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]
A​
[/td][td="bgcolor: #DCE6F1"]
B​
[/td][td="bgcolor: #DCE6F1"]
C​
[/td][td="bgcolor: #DCE6F1"]
D​
[/td][td="bgcolor: #DCE6F1"]
E​
[/td][td="bgcolor: #DCE6F1"]
F​
[/td][td="bgcolor: #DCE6F1"]
G​
[/td][td="bgcolor: #DCE6F1"]
H​
[/td][td="bgcolor: #DCE6F1"]
I​
[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]
1​
[/td][td][/td][td]
June​
[/td][td]
July​
[/td][td]
August​
[/td][td][/td][td][/td][td]
June​
[/td][td]
July​
[/td][td]
August​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
2​
[/td][td]
ProductCode3​
[/td][td]
200​
[/td][td]
90​
[/td][td]
350​
[/td][td][/td][td]
Ingredient1​
[/td][td="bgcolor:#D9D9D9"]
160​
[/td][td="bgcolor:#D9D9D9"]
83​
[/td][td="bgcolor:#D9D9D9"]
171​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
3​
[/td][td]
ProductCode2​
[/td][td]
150​
[/td][td]
80​
[/td][td]
120​
[/td][td][/td][td]
Ingredient2​
[/td][td="bgcolor:#D9D9D9"]
75​
[/td][td="bgcolor:#D9D9D9"]
36,5​
[/td][td="bgcolor:#D9D9D9"]
104​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
4​
[/td][td]
ProductCode1​
[/td][td]
100​
[/td][td]
50​
[/td][td]
200​
[/td][td][/td][td]
Ingredient3​
[/td][td="bgcolor:#D9D9D9"]
60​
[/td][td="bgcolor:#D9D9D9"]
27​
[/td][td="bgcolor:#D9D9D9"]
105​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
5​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]
Ingredient4​
[/td][td="bgcolor:#D9D9D9"]
155​
[/td][td="bgcolor:#D9D9D9"]
73,5​
[/td][td="bgcolor:#D9D9D9"]
290​
[/td][/tr]
[/table]

Regular formula in G2 copied across and down
=SUMPRODUCT(B$2:B$4,LOOKUP($A$2:$A$4,Sheet2!$A$2:$A$4,INDEX(Sheet2!$B$2:$E$4,0,MATCH($F2,Sheet2!$B$1:$E$1,0))))
confirmed with just Enter

M.
 
Upvote 0
Marcelo Branco said:
This should complicate the formula. Could you provide an example along with the expected results?

By the way, we can simplify the original formula with a different configuration and as long as the product codes in Sheet2 column A are in ascending order.
Something like,

Sheet1

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]
A​
[/TD]
[TD="bgcolor: #DCE6F1"]
B​
[/TD]
[TD="bgcolor: #DCE6F1"]
C​
[/TD]
[TD="bgcolor: #DCE6F1"]
D​
[/TD]
[TD="bgcolor: #DCE6F1"]
E​
[/TD]
[TD="bgcolor: #DCE6F1"]
F​
[/TD]
[TD="bgcolor: #DCE6F1"]
G​
[/TD]
[TD="bgcolor: #DCE6F1"]
H​
[/TD]
[TD="bgcolor: #DCE6F1"]
I​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
1​
[/TD]
[TD][/TD]
[TD]
June​
[/TD]
[TD]
July​
[/TD]
[TD]
August​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
June​
[/TD]
[TD]
July​
[/TD]
[TD]
August​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
2​
[/TD]
[TD]
ProductCode3​
[/TD]
[TD]
200​
[/TD]
[TD]
90​
[/TD]
[TD]
350​
[/TD]
[TD][/TD]
[TD]
Ingredient1​
[/TD]
[TD="bgcolor: #D9D9D9"]
160​
[/TD]
[TD="bgcolor: #D9D9D9"]
83​
[/TD]
[TD="bgcolor: #D9D9D9"]
171​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
3​
[/TD]
[TD]
ProductCode2​
[/TD]
[TD]
150​
[/TD]
[TD]
80​
[/TD]
[TD]
120​
[/TD]
[TD][/TD]
[TD]
Ingredient2​
[/TD]
[TD="bgcolor: #D9D9D9"]
75​
[/TD]
[TD="bgcolor: #D9D9D9"]
36,5​
[/TD]
[TD="bgcolor: #D9D9D9"]
104​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
4​
[/TD]
[TD]
ProductCode1​
[/TD]
[TD]
100​
[/TD]
[TD]
50​
[/TD]
[TD]
200​
[/TD]
[TD][/TD]
[TD]
Ingredient3​
[/TD]
[TD="bgcolor: #D9D9D9"]
60​
[/TD]
[TD="bgcolor: #D9D9D9"]
27​
[/TD]
[TD="bgcolor: #D9D9D9"]
105​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
5​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
Ingredient4​
[/TD]
[TD="bgcolor: #D9D9D9"]
155​
[/TD]
[TD="bgcolor: #D9D9D9"]
73,5​
[/TD]
[TD="bgcolor: #D9D9D9"]
290​
[/TD]
[/TR]
</tbody>[/TABLE]


Regular formula in G2 copied across and down
=SUMPRODUCT(B$2:B$4,LOOKUP($A$2:$A$4,Sheet2!$A$2:$A$4,INDEX(Sheet2!$B$2:$E$4,0,MATCH($F2,Sheet2!$B$1:$E$1,0))))
confirmed with just Enter

M.
Click to expand...
Hi Marcelo,

Thx for your quick response.
Would look something like the below. To keep it simple the results would be the same as before, but I would be able to exclude ProductCodes from the array when they 1) are not produced on line 1 and 2) don't have the colour black.
In essence, I only look for the ingredient volume outcome of all products where those criteria are met:

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"]
A​
[/TD]
[TD="bgcolor: #DCE6F1"]
B​
[/TD]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]
C​
[/TD]
[TD="bgcolor: #DCE6F1"]
D​
[/TD]
[TD="bgcolor: #DCE6F1"]
E​
[/TD]
[TD="bgcolor: #DCE6F1"]
F​
[/TD]
[TD="bgcolor: #DCE6F1"]
G​
[/TD]
[TD="bgcolor: #DCE6F1"]
H​
[/TD]
[TD="bgcolor: #DCE6F1"]
I​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
1​
[/TD]
[TD][/TD]
[TD]Line[/TD]
[TD]Colour[/TD]
[TD]June[/TD]
[TD]July[/TD]
[TD]August[/TD]
[TD][/TD]
[TD][/TD]
[TD]June[/TD]
[TD]July[/TD]
[TD]August[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
2​
[/TD]
[TD]ProductCode3[/TD]
[TD]1[/TD]
[TD]Black[/TD]
[TD]
200​
[/TD]
[TD]
90​
[/TD]
[TD]
350​
[/TD]
[TD][/TD]
[TD]Ingredient1[/TD]
[TD="bgcolor: #D9D9D9"]
160​
[/TD]
[TD="bgcolor: #D9D9D9"]
83​
[/TD]
[TD="bgcolor: #D9D9D9"]
171​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
3​
[/TD]
[TD]ProductCode2[/TD]
[TD]1[/TD]
[TD]Black[/TD]
[TD]
150​
[/TD]
[TD]
80​
[/TD]
[TD]
120​
[/TD]
[TD][/TD]
[TD]Ingredient2[/TD]
[TD="bgcolor: #D9D9D9"]
75​
[/TD]
[TD="bgcolor: #D9D9D9"]
36,5​
[/TD]
[TD="bgcolor: #D9D9D9"]
104​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
4​
[/TD]
[TD]ProductCode1[/TD]
[TD]1[/TD]
[TD]Black[/TD]
[TD]
100​
[/TD]
[TD]
50​
[/TD]
[TD]
200​
[/TD]
[TD][/TD]
[TD]Ingredient3[/TD]
[TD="bgcolor: #D9D9D9"]
60​
[/TD]
[TD="bgcolor: #D9D9D9"]
27​
[/TD]
[TD="bgcolor: #D9D9D9"]
105​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
5​
[/TD]
[TD]ProductCode4[/TD]
[TD]2[/TD]
[TD]Orange[/TD]
[TD]20[/TD]
[TD]80[/TD]
[TD]20[/TD]
[TD][/TD]
[TD]Ingredient4[/TD]
[TD="bgcolor: #D9D9D9"]
155​
[/TD]
[TD="bgcolor: #D9D9D9"]
73,5​
[/TD]
[TD="bgcolor: #D9D9D9"]
290​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]6[/TD]
[TD]ProductCode5[/TD]
[TD]1[/TD]
[TD]Blue[/TD]
[TD]30[/TD]
[TD]30[/TD]
[TD]30[/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[/TR]
</tbody>[/TABLE]

****** id="cke_pastebin" style="position: absolute; top: -3.05176e-05px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">[TABLE="class: cms_table_grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"]
A​
[/TD]
[TD="bgcolor: #DCE6F1"]
B​
[/TD]
[TD="bgcolor: #DCE6F1"]
C​
[/TD]
[TD="bgcolor: #DCE6F1"]
D​
[/TD]
[TD="bgcolor: #DCE6F1"]
E​
[/TD]
[TD="bgcolor: #DCE6F1"]
F​
[/TD]
[TD="bgcolor: #DCE6F1"]
G​
[/TD]
[TD="bgcolor: #DCE6F1"]
H​
[/TD]
[TD="bgcolor: #DCE6F1"]
I​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
1​
[/TD]
[TD][/TD]
[TD]June
[/TD]
[TD]July
[/TD]
[TD]August
[/TD]
[TD][/TD]
[TD][/TD]
[TD]June
[/TD]
[TD]July
[/TD]
[TD]August
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
2​
[/TD]
[TD]ProductCode3
[/TD]
[TD]
200​
[/TD]
[TD]
90​
[/TD]
[TD]
350​
[/TD]
[TD][/TD]
[TD]Ingredient1
[/TD]
[TD="bgcolor: #D9D9D9"]
160​
[/TD]
[TD="bgcolor: #D9D9D9"]
83​
[/TD]
[TD="bgcolor: #D9D9D9"]
171​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
3​
[/TD]
[TD]ProductCode2
[/TD]
[TD]
150​
[/TD]
[TD]
80​
[/TD]
[TD]
120​
[/TD]
[TD][/TD]
[TD]Ingredient2
[/TD]
[TD="bgcolor: #D9D9D9"]
75​
[/TD]
[TD="bgcolor: #D9D9D9"]
36,5​
[/TD]
[TD="bgcolor: #D9D9D9"]
104​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
4​
[/TD]
[TD]ProductCode1
[/TD]
[TD]
100​
[/TD]
[TD]
50​
[/TD]
[TD]
200​
[/TD]
[TD][/TD]
[TD]Ingredient3
[/TD]
[TD="bgcolor: #D9D9D9"]
60​
[/TD]
[TD="bgcolor: #D9D9D9"]
27​
[/TD]
[TD="bgcolor: #D9D9D9"]
105​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
5​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Ingredient4
[/TD]
[TD="bgcolor: #D9D9D9"]
155​
[/TD]
[TD="bgcolor: #D9D9D9"]
73,5​
[/TD]
[TD="bgcolor: #D9D9D9"]
290​
[/TD]
[/TR]
</tbody>[/TABLE]
</body>Would that be possible?
 
Upvote 0
New version - doesn't require Product Codes in ascending order (Column A of Sheet2)

Sheet2 ( i changed the rows of ProductCode2 and ProductCode1 for testing purposes)

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]
A​
[/TD]
[TD="bgcolor: #DCE6F1"]
B​
[/TD]
[TD="bgcolor: #DCE6F1"]
C​
[/TD]
[TD="bgcolor: #DCE6F1"]
D​
[/TD]
[TD="bgcolor: #DCE6F1"]
E​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
1​
[/TD]
[TD][/TD]
[TD]
Ingredient1​
[/TD]
[TD]
Ingredient2​
[/TD]
[TD]
Ingredient3​
[/TD]
[TD]
Ingredient4​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
2​
[/TD]
[TD]
ProductCode2​
[/TD]
[TD]
80%​
[/TD]
[TD]
20%​
[/TD]
[TD]
0%​
[/TD]
[TD]
0%​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
3​
[/TD]
[TD]
ProductCode1​
[/TD]
[TD]
20%​
[/TD]
[TD]
5%​
[/TD]
[TD]
0%​
[/TD]
[TD]
75%​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
4​
[/TD]
[TD]
ProductCode3​
[/TD]
[TD]
10%​
[/TD]
[TD]
20%​
[/TD]
[TD]
30%​
[/TD]
[TD]
40%​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
5​
[/TD]
[TD]
ProductCode4​
[/TD]
[TD]
60%​
[/TD]
[TD]
10%​
[/TD]
[TD]
20%​
[/TD]
[TD]
10%​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
6​
[/TD]
[TD]
ProductCode5​
[/TD]
[TD]
20%​
[/TD]
[TD]
30%​
[/TD]
[TD]
10%​
[/TD]
[TD]
40%
​
[/TD]
[/TR]
</tbody>[/TABLE]


Sheet1 (Criteria: line 1 or 2; Colour = Black)

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]
A​
[/TD]
[TD="bgcolor: #DCE6F1"]
B​
[/TD]
[TD="bgcolor: #DCE6F1"]
C​
[/TD]
[TD="bgcolor: #DCE6F1"]
D​
[/TD]
[TD="bgcolor: #DCE6F1"]
E​
[/TD]
[TD="bgcolor: #DCE6F1"]
F​
[/TD]
[TD="bgcolor: #DCE6F1"]
G​
[/TD]
[TD="bgcolor: #DCE6F1"]
H​
[/TD]
[TD="bgcolor: #DCE6F1"]
I​
[/TD]
[TD="bgcolor: #DCE6F1"]
J​
[/TD]
[TD="bgcolor: #DCE6F1"]
K​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
1​
[/TD]
[TD][/TD]
[TD]
Line​
[/TD]
[TD]
Colour​
[/TD]
[TD]
June​
[/TD]
[TD]
July​
[/TD]
[TD]
August​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
June​
[/TD]
[TD]
July​
[/TD]
[TD]
August​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
2​
[/TD]
[TD]
ProductCode3​
[/TD]
[TD]
1​
[/TD]
[TD]
Black​
[/TD]
[TD]
200​
[/TD]
[TD]
90​
[/TD]
[TD]
350​
[/TD]
[TD][/TD]
[TD]
Ingredient1​
[/TD]
[TD]
160​
[/TD]
[TD]
83​
[/TD]
[TD]
171​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
3​
[/TD]
[TD]
ProductCode2​
[/TD]
[TD]
1​
[/TD]
[TD]
Black​
[/TD]
[TD]
150​
[/TD]
[TD]
80​
[/TD]
[TD]
120​
[/TD]
[TD][/TD]
[TD]
Ingredient2​
[/TD]
[TD]
75​
[/TD]
[TD]
36,5​
[/TD]
[TD]
104​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
4​
[/TD]
[TD]
ProductCode1​
[/TD]
[TD]
1​
[/TD]
[TD]
Black​
[/TD]
[TD]
100​
[/TD]
[TD]
50​
[/TD]
[TD]
200​
[/TD]
[TD][/TD]
[TD]
Ingredient3​
[/TD]
[TD]
60​
[/TD]
[TD]
27​
[/TD]
[TD]
105​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
5​
[/TD]
[TD]
ProductCode4​
[/TD]
[TD]
2​
[/TD]
[TD]
Orange​
[/TD]
[TD]
20​
[/TD]
[TD]
80​
[/TD]
[TD]
20​
[/TD]
[TD][/TD]
[TD]
Ingredient4​
[/TD]
[TD]
155​
[/TD]
[TD]
73,5​
[/TD]
[TD]
290​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
6​
[/TD]
[TD]
ProductCode5​
[/TD]
[TD]
1​
[/TD]
[TD]
Blue​
[/TD]
[TD]
30​
[/TD]
[TD]
30​
[/TD]
[TD]
30​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Formula in I2 copied across and down
=SUMPRODUCT(D$2:D$6,SUMIF(Sheet2!$A$2:$A$6,$A$2:$A$6,INDEX(Sheet2!$B$2:$E$6,0,MATCH($H2,Sheet2!$B$1:$E$1,0))),--(($B$2:$B$6=1)+($B$2:$B$6=2)),--($C$2:$C$6="Black"))

M.
 
Last edited:
Upvote 0
Thanks Marcelo,

It's not yet working, wondering if I'm doing something wrong.
Could something potentially be missed around the index match piece of the formula?
Notice it's not picking up those 2 ranges in recognizable colours in the formula (but just guessing)

Thanks a lot for your help so far!
 
Upvote 0
Worked perfectly for me.

What you mean by not working? An error, wrong result,...?
Could you show us again the table in Sheet2?

M.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question Question
Formula Help needed
Replies
1
Views
520
elemenopee
E
B
  • Question Question
Index, Match expert help needed.
2
Replies
15
Views
897
Jonmo1
Jonmo1

Forum statistics

Threads
1,223,888
Messages
6,175,219
Members
452,619
Latest member
Shiv1198

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top