Sum if lookup value matches

[jayjay226]

Hi everyone

Hoping someone can help...

I have 2 tables on different sheets like so:

Sheet 1

	A	B	C	D	E	F	G
1		Jan	Feb	Mar	Apr	May	Jun
2	Apples	10% Off	25% Off	20% Off	20% Off	10% Off	20% Off
3	Oranges	20% Off	25% Off	10% Off	20% Off	10% Off	20% Off
4	Bananas	25% Off	20% Off	10% Off	25% Off	10% Off	25% Off

<tbody>
</tbody>

Sheet 2

	A	B	C	D	E	F	G	H	I	J
1		Jan	Feb	Mar	Apr	May	Jun	10% Off	20% Off	25% Off
2	Apples	20	35	42	10	12	13
3	Oranges	15	35	10	9	56	12
4	Bananas	5	6	7	18	4	12

<tbody>
</tbody>

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.