Hi, I'm trying to sum every 3rd column up to the current week in order to have a current year to date sum up to our current fiscal week (or any fiscal week chosen). Below is the current setup, any ideas with sumproduct, offset, mod probably make sense but I can't wrap my head around what the combo looks like.
<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>
<colgroup><col style="width: 60px"><col width="60"><col width="60"><col width="21"><col width="21"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"></colgroup><tbody>
</tbody>
Ideally i'd type a week number into B2 and that would update both A4 and B4 to reflect Plan and Actual up to that point. As you can see to the right we have each fiscal week with Plan and Actual, so I'd only need to sum every 3rd column up to the (week number) of columns.
Thanks
Nick
<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>
YTD as of | 22 | FW 01 | FW 01-2 | FW 01-3 | FW 02 | FW 02-2 | FW 02-3 | FW 03 | FW 03-2 | FW 03-3 | FW 04 | FW 04-2 | FW 04-3 | FW 05 | FW 05-2 | FW 05-3 | FW 06 | FW 06-2 | FW 06-3 | |||
12/31/18 - 12/29/19 | Jan | 12/31 | 1/6 | Jan | 1/7 | 1/13 | Jan | 1/14 | 1/20 | Jan | 1/21 | 1/27 | Feb | 1/28 | 2/3 | Feb | 2/4 | 2/10 | ||||
Plan | Actual | Plan | Forecast | Actual | Plan | Forecast | Actual | Plan | Forecast | Actual | Plan | Forecast | Actual | Plan | Forecast | Actual | Plan | Forecast | Actual | |||
#VALUE! | 1,746.00 | 1,035.50 | 2,037.00 | 3,375.50 | 2,037.00 | 627.50 | 2,037.00 | 2,553.75 | 2,932.39 | 13,796.00 | 4,126.25 | 6,026.00 |
<colgroup><col style="width: 60px"><col width="60"><col width="60"><col width="21"><col width="21"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"></colgroup><tbody>
</tbody>
Ideally i'd type a week number into B2 and that would update both A4 and B4 to reflect Plan and Actual up to that point. As you can see to the right we have each fiscal week with Plan and Actual, so I'd only need to sum every 3rd column up to the (week number) of columns.
Thanks
Nick