Hlookup and Sumif - help!!!

[rashid67]

Help !! I want to combine Hlookup and sumif to summaries the data by looking up months in sheet 2 using hlookup and sum all the numbers by category in sheet 1 - summary.

Please see the example below.

SHEET 1 - Summary:
[TABLE="width: 329"]
<tbody>[TR]
[TD]Category[/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[/TR]
[TR]
[TD]Salary[/TD]
[TD="align: right"]700[/TD]
[TD="align: right"]1500[/TD]
[TD="align: right"]2300[/TD]
[/TR]
[TR]
[TD]Over time[/TD]
[TD="align: right"]110[/TD]
[TD="align: right"]190[/TD]
[TD="align: right"]270[/TD]
[/TR]
[TR]
[TD]Benefits[/TD]
[TD="align: right"]52[/TD]
[TD="align: right"]84[/TD]
[TD="align: right"]116[/TD]
[/TR]
</tbody>[/TABLE]


SHEET 2 - Data
[TABLE="width: 842"]
<tbody>[TR]
[TD][TABLE="width: 906"]
<tbody>[TR]
[TD][/TD]
[TD]Jan[/TD]
[TD]Jan[/TD]
[TD]Jan[/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Feb[/TD]
[TD]Feb[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Mar[/TD]
[TD]Mar[/TD]
[TD]Mar[/TD]
[/TR]
[TR]
[TD]Category[/TD]
[TD]Wk1[/TD]
[TD]Wk2[/TD]
[TD]Wk3[/TD]
[TD]Wk4[/TD]
[TD]Wk1[/TD]
[TD]Wk2[/TD]
[TD]Wk3[/TD]
[TD]Wk4[/TD]
[TD]Wk1[/TD]
[TD]Wk2[/TD]
[TD]Wk3[/TD]
[TD]Wk4[/TD]
[/TR]
[TR]
[TD]Salary[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]250[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]350[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]450[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]550[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]650[/TD]
[/TR]
[TR]
[TD]Over time[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]75[/TD]
[/TR]
[TR]
[TD]Benefits[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]32[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[kennedy_clan]

How about entering in the "Jan" Summary cell:
=SUMPRODUCT((Sheet2!$B$1:$M$1=B$1)*(Sheet2!$A$3:$A$5=$A2)*Sheet2!$B$3:$M$5). The fill down and fill right

above formula assume that:
Sheet1: Category in column A, Month in Row 1
Sheet2: Category in column A, Month in Row 1, Wk in Row 2, values in B3:M5.

Hope that makes sense.