Sum every nth column for x instances (to get a weekly YTD number)

nickcar

New Member
Joined
Jun 6, 2019
Messages
4
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>
YTD as of22FW 01FW 01-2FW 01-3FW 02FW 02-2FW 02-3FW 03FW 03-2FW 03-3FW 04FW 04-2FW 04-3FW 05FW 05-2FW 05-3FW 06FW 06-2FW 06-3
12/31/18 - 12/29/19Jan12/311/6Jan1/71/13Jan1/141/20Jan1/211/27Feb1/282/3Feb2/42/10
PlanActualPlanForecastActualPlanForecastActualPlanForecastActualPlanForecastActualPlanForecastActualPlanForecastActual
#VALUE!1,746.001,035.502,037.003,375.502,037.00627.502,037.002,553.752,932.3913,796.004,126.256,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
 

kweaver

Well-known Member
Joined
May 8, 2018
Messages
838
Office Version
365, 2010
I can't tell from your illustration which column contains which information.
However, if you want to sum the "Actuals" (in green) values below them:

Code:
=SUMPRODUCT(("Actual" = F4:W4)*F5:W5)
Where in my sheet, the first indication of Plan/Forecast/Actual began in F4 (with Plan), etc.
 
Last edited:

nickcar

New Member
Joined
Jun 6, 2019
Messages
4
Thanks kweaver that makes good sense. How would I limit that sumproduct to only the first x number of instances? So only up to week 22 of the 52 fiscal weeks of the year?
 

kweaver

Well-known Member
Joined
May 8, 2018
Messages
838
Office Version
365, 2010
In your example you're showing 6 sets of 3 columns (Plan, Forecast, Actual -- repeat).
But I cannot tell from the display if that represents 6 months (Jan, Feb, etc.) or what...so that the 22nd week would be where?
 

nickcar

New Member
Joined
Jun 6, 2019
Messages
4
Good question, what I pasted in is only the first 6 weeks of a 52 week year, the rest of the weeks continue in similar columns to the right. Ideally i’d reference a week number and have a YTD sum through that week.
 

kweaver

Well-known Member
Joined
May 8, 2018
Messages
838
Office Version
365, 2010
I put the number of weeks (e.g., 3) in C2.
Then, in A5 (below "Plan") I have:

Code:
=SUMPRODUCT(("Plan"=INDIRECT("$F$4:"&(ADDRESS(4,5+3*$C$2))))*INDIRECT("$F$5:"&ADDRESS(5,5+3*$C$2)))
And in B5 (below "Actual") I have:

Code:
=SUMPRODUCT(("Actual"=INDIRECT("$F$4:"&(ADDRESS(4,5+3*$C$2))))*INDIRECT("$F$5:"&ADDRESS(5,5+3*$C$2)))
As I mentioned in a previous post, my blocks of 3 start in F column.
 
Last edited:

kweaver

Well-known Member
Joined
May 8, 2018
Messages
838
Office Version
365, 2010
I shifted everything to the left to match what I now figured out are your column references.

Code:
=SUMPRODUCT(("Plan"=INDIRECT("$E$4:"&(ADDRESS(4,4+3*$B$2))))*INDIRECT("$E$5:"&ADDRESS(5,4+3*$B$2)))
The above code now goes into A5 under "Plan"...and the same code would be under "Actual" in B5 but you need to change the "Plan" to "Actual"

Hope this helps.

Kevin
 
Last edited:

nickcar

New Member
Joined
Jun 6, 2019
Messages
4
Awesome, yeah, I get it and was playing with it as well. Is there a way to make the cell references in quotes to be dynamic, so that if i dragged that formula down it'd reference it's current row? I used the row() function to do that within the address() function...
 

Nishant94

Well-known Member
Joined
May 8, 2015
Messages
507
Maybe something like this:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th><th>P</th><th>Q</th><th>R</th><th>S</th><th>T</th><th>U</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="color: #333333;background-color: #FAFAFA;;">YTD as of</td><td style="text-align: center;color: #333333;background-color: #FAFAFA;;">22</td><td style="text-align: right;color: #333333;background-color: #000000;;"></td><td style="color: #333333;background-color: #FAFAFA;;">FW 01</td><td style="color: #333333;background-color: #FAFAFA;;">FW 01-2</td><td style="color: #333333;background-color: #FAFAFA;;">FW 01-3</td><td style="color: #333333;background-color: #FAFAFA;;">FW 02</td><td style="color: #333333;background-color: #FAFAFA;;">FW 02-2</td><td style="color: #333333;background-color: #FAFAFA;;">FW 02-3</td><td style="color: #333333;background-color: #FAFAFA;;">FW 03</td><td style="color: #333333;background-color: #FAFAFA;;">FW 03-2</td><td style="color: #333333;background-color: #FAFAFA;;">FW 03-3</td><td style="color: #333333;background-color: #FAFAFA;;">FW 04</td><td style="color: #333333;background-color: #FAFAFA;;">FW 04-2</td><td style="color: #333333;background-color: #FAFAFA;;">FW 04-3</td><td style="color: #333333;background-color: #FAFAFA;;">FW 05</td><td style="color: #333333;background-color: #FAFAFA;;">FW 05-2</td><td style="color: #333333;background-color: #FAFAFA;;">FW 05-3</td><td style="color: #333333;background-color: #FAFAFA;;">FW 06</td><td style="color: #333333;background-color: #FAFAFA;;">FW 06-2</td><td style="color: #333333;background-color: #FAFAFA;;">FW 06-3</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="color: #333333;background-color: #FAFAFA;;">12/31/18 - 12/29/19</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #000000;;"></td><td style="color: #333333;background-color: #FAFAFA;;">Jan</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">Dec-31</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">01-Jun</td><td style="color: #333333;background-color: #FAFAFA;;">Jan</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">01-Jul</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">Jan-13</td><td style="color: #333333;background-color: #FAFAFA;;">Jan</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">Jan-14</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">Jan-20</td><td style="color: #333333;background-color: #FAFAFA;;">Jan</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">Jan-21</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">Jan-27</td><td style="color: #333333;background-color: #FAFAFA;;">Feb</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">Jan-28</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">02-Mar</td><td style="color: #333333;background-color: #FAFAFA;;">Feb</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">02-Apr</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">02-Oct</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: center;color: #333333;background-color: #FAFAFA;;">Plan</td><td style="text-align: center;color: #333333;background-color: #FAFAFA;;">Actual</td><td style="text-align: right;color: #333333;background-color: #000000;;"></td><td style="color: #333333;background-color: #E91D63;;">Plan</td><td style="color: #333333;background-color: #F7CB4D;;">Forecast</td><td style="color: #333333;background-color: #8BC34A;;">Actual</td><td style="color: #333333;background-color: #E91D63;;">Plan</td><td style="color: #333333;background-color: #F7CB4D;;">Forecast</td><td style="color: #333333;background-color: #8BC34A;;">Actual</td><td style="color: #333333;background-color: #E91D63;;">Plan</td><td style="color: #333333;background-color: #F7CB4D;;">Forecast</td><td style="color: #333333;background-color: #8BC34A;;">Actual</td><td style="color: #333333;background-color: #E91D63;;">Plan</td><td style="color: #333333;background-color: #F7CB4D;;">Forecast</td><td style="color: #333333;background-color: #8BC34A;;">Actual</td><td style="color: #333333;background-color: #E91D63;;">Plan</td><td style="color: #333333;background-color: #F7CB4D;;">Forecast</td><td style="color: #333333;background-color: #8BC34A;;">Actual</td><td style="color: #333333;background-color: #E91D63;;">Plan</td><td style="color: #333333;background-color: #F7CB4D;;">Forecast</td><td style="color: #333333;background-color: #8BC34A;;">Actual</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: center;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #000000;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">1,746.00</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">1,035.50</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">2,037.00</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">3,375.50</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">2,037.00</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">627.5</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">2,037.00</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">2,553.75</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">2,932.39</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">13,796.00</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">4,126.25</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">6,026.00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;"></td><td style=";">YTD Total</td><td style="text-align: right;;">27414.25</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C6</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">SUMIFS(<font color="Red">D4:U4,D3:U3,"Actual",B1:S1,"FW "&TEXT(<font color="Green">ROW(<font color="Purple">A1:INDEX(<font color="Teal">A:A,B1</font>)</font>),"00"</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

Forum statistics

Threads
1,085,198
Messages
5,382,268
Members
401,781
Latest member
lozzeroooni

Some videos you may like

This Week's Hot Topics

Top