I have table which has labour information from the current financial
year, and one of my colleagues is wanting to look at some of the
information a different way. The following is an example of a portion of
the information and what I'm trying to achieve...
I have the following data on Sheet1 in cells A1:G18:
Site | Type | Organisation Unit | Wk30 | Wk31 | Wk32 | Wk33 |
AL | | AL Albany | | | | 7.25 |
AL | | AL Mainstream | 43.57 | 23.17 | 107.37 | 13.82 |
AL | | AL Bar | 66.08 | 48.20 | 49.53 | 28.08 |
AL | | AL Floor | 275.57 | 238.50 | 203.50 | 173.03 |
AL | | AL POS | 418.72 | 422.43 | 348.38 | 303.97 |
AL | | AL Management | 140.98 | 129.78 | 159.17 | 134.30 |
AL | PROJ | AL Projection | 111.05 | 109.73 | 97.88 | 86.05 |
ALGC | | ALGC Gold Class | | | | |
ALGC | | ALGC Management | 100.42 | 116.32 | 89.58 | 82.55 |
ALGC | | ALGC Service | 457.97 | 476.93 | 384.33 | 308.10 |
BW | | BW NewMarket / Broadway | 8.00 | 32.00 | 8.00 | |
BW | | BW Mainstream | 199.82 | 55.85 | 41.35 | 27.27 |
BW | | BW Bar | 25.85 | 21.93 | 20.23 | 25.72 |
BW | | BW Floor | 92.87 | 84.72 | 85.07 | 76.38 |
BW | | BW POS | 121.73 | 210.75 | 203.20 | 167.70 |
BW | | BW Management | 117.97 | 126.37 | 108.43 | 133.33 |
BW | PROJ | BW Projection | 99.60 | 86.65 | 95.17 | 104.53 |
<colgroup><col><col><col><col span="4"></colgroup><tbody>
</tbody>
On Sheet2 I have the following table in cells A1:E4:
<table style="width: 445px;" border="0" cellSpacing="0" cellPadding="0"><colgroup><col span="5"></colgroup><tbody></tbody>
PROJ | | | | |
WK END | MONTH | WEEK | AL | BW |
2013-01-30 | JUL | Wk30 | | |
2013-02-06 | JUL | Wk31 | | |
<colgroup><col span="5"></colgroup><tbody>
</tbody>
On Sheet2 in cell D3 I've tried the formula:
=SUMIFS(INDEX('Sheet1'!$D$1:$G$18,0,MATCH('Sheet2'!$C3,'Sheet1'!$D$1:$G$1,0)),'Sheet1'!$A:$A,'Sheet2'!D$2,'Sheet1'!$B:$B,'Sheet2'!$A$1)
What
I'm after is a formula that I can copy from cell D3 to cells D4, as
well as to C3:C4 that sums information from Sheet1 when the Site &
Type match from columns A&B, and the sum_range (and I hope this
makes sense) is variable, dependent on the week from Sheet2.
I'm not sure if the sumifs formula is the best one to use or if I've written it correctly.