Sum if lookup value matches

[jayjay226]

Hi everyone

Hoping someone can help...

I have 2 tables on different sheets like so:

Sheet 1
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD]Jun[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Apples
[/TD]
[TD]10% Off[/TD]
[TD]25% Off[/TD]
[TD]20% Off[/TD]
[TD]20% Off[/TD]
[TD]10% Off[/TD]
[TD]20% Off[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Oranges[/TD]
[TD]20% Off[/TD]
[TD]25% Off[/TD]
[TD]10% Off[/TD]
[TD]20% Off[/TD]
[TD]10% Off[/TD]
[TD]20% Off[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Bananas[/TD]
[TD]25% Off[/TD]
[TD]20% Off[/TD]
[TD]10% Off[/TD]
[TD]25% Off[/TD]
[TD]10% Off[/TD]
[TD]25% Off[/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD]Jun[/TD]
[TD]10% Off[/TD]
[TD]20% Off[/TD]
[TD]25% Off[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Apples[/TD]
[TD]20[/TD]
[TD]35[/TD]
[TD]42[/TD]
[TD]10[/TD]
[TD]12[/TD]
[TD]13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Oranges[/TD]
[TD]15[/TD]
[TD]35[/TD]
[TD]10[/TD]
[TD]9[/TD]
[TD]56[/TD]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Bananas[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]18[/TD]
[TD]4[/TD]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

In cells H2:J4, I want to sum the values in B:G if the lookup value for that month in table 1 matches the heading in H1:J1

Sheet 2 is a lot longer than Sheet 1 with the names appearing multiple times so I don't think I can just do a basic SUMIF

Thanks in advance for you help

 

[Aladin Akyurek]

What would be the expected values if you compute manually using the rule you specify?

 

[jayjay226]

Values for H2:J2 (Apples) should be 32, 65 and 35 respectively
Values for H3:J3 (Oranges) should be 66, 36 and 35 resepctively

What I'm trying to do is find out from table 1 which months we ran the promotion in the heading (e.g. "10% off) for that product, then sum up the values for the matching months.

So the Apples line for example, under "10% off" it would add up the values for Jan and May, 20% would add up Mar, Apr, and June, and so on

Thanks again

 

[Aladin Akyurek]

Thanks. Does Apples occur just once in both sheets? Or possibly more than once on Sheet1?

 

[jayjay226]

Each product appears only once in Sheet 1 (sort of like a master list) but will appear multiple times in the sales data in Sheet 2 with different values

Thanks

 

[Aladin Akyurek]

In H2 of Sheet2 control+shift+enter, not just enter, copy across, and down:

=SUM(IF(Sheet1!$A$2:$A$4=$A2,IF(Sheet1!$B$2:$G$4=H$1,$B2:$G2)))

 

[Aladin Akyurek]

An afterthought, using SUMIFS...

In H2 of Sheet2 just enter, copy across, and down:

=SUMIFS($B2:$G2,INDEX(Sheet1!$B$2:$G$4,MATCH($A2,Sheet1!$A$2:$A$4,0),0),H$1)

which would be a tad faster.

 

[jayjay226]

Wow that was perfect - thanks heaps!!

 

[Aladin Akyurek]

Wow that was perfect - thanks heaps!!

You are welcome.