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

N

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>[TABLE="width: 0"]
<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>[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=999999]#999999[/URL] , colspan: 2, align: center"]YTD as of[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=999999]#999999[/URL] , align: center"]22[/TD]
[TD="bgcolor: #000000"][/TD]
[TD="bgcolor: #000000"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B7B7B7]#B7B7B7[/URL] "]FW 01[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B7B7B7]#B7B7B7[/URL] "]FW 01-2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B7B7B7]#B7B7B7[/URL] "]FW 01-3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B7B7B7]#B7B7B7[/URL] "]FW 02[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B7B7B7]#B7B7B7[/URL] "]FW 02-2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B7B7B7]#B7B7B7[/URL] "]FW 02-3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B7B7B7]#B7B7B7[/URL] "]FW 03[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B7B7B7]#B7B7B7[/URL] "]FW 03-2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B7B7B7]#B7B7B7[/URL] "]FW 03-3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B7B7B7]#B7B7B7[/URL] "]FW 04[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B7B7B7]#B7B7B7[/URL] "]FW 04-2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B7B7B7]#B7B7B7[/URL] "]FW 04-3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B7B7B7]#B7B7B7[/URL] "]FW 05[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B7B7B7]#B7B7B7[/URL] "]FW 05-2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B7B7B7]#B7B7B7[/URL] "]FW 05-3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B7B7B7]#B7B7B7[/URL] "]FW 06[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B7B7B7]#B7B7B7[/URL] "]FW 06-2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B7B7B7]#B7B7B7[/URL] "]FW 06-3[/TD]
[/TR]
[TR]
[TD="colspan: 3, align: center"]12/31/18 - 12/29/19[/TD]
[TD="bgcolor: #000000"][/TD]
[TD="bgcolor: #000000"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B7B7B7]#B7B7B7[/URL] "]Jan[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B7B7B7]#B7B7B7[/URL] , align: right"]12/31[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B7B7B7]#B7B7B7[/URL] , align: right"]1/6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B7B7B7]#B7B7B7[/URL] "]Jan[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B7B7B7]#B7B7B7[/URL] , align: right"]1/7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B7B7B7]#B7B7B7[/URL] , align: right"]1/13[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B7B7B7]#B7B7B7[/URL] "]Jan[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B7B7B7]#B7B7B7[/URL] , align: right"]1/14[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B7B7B7]#B7B7B7[/URL] , align: right"]1/20[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B7B7B7]#B7B7B7[/URL] "]Jan[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B7B7B7]#B7B7B7[/URL] , align: right"]1/21[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B7B7B7]#B7B7B7[/URL] , align: right"]1/27[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B7B7B7]#B7B7B7[/URL] "]Feb[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B7B7B7]#B7B7B7[/URL] , align: right"]1/28[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B7B7B7]#B7B7B7[/URL] , align: right"]2/3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B7B7B7]#B7B7B7[/URL] "]Feb[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B7B7B7]#B7B7B7[/URL] , align: right"]2/4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B7B7B7]#B7B7B7[/URL] , align: right"]2/10[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: center"]Plan[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: center"]Actual[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: center"][/TD]
[TD="bgcolor: #000000"][/TD]
[TD="bgcolor: #000000"][/TD]
[TD="bgcolor: #E91D63"]Plan[/TD]
[TD="bgcolor: #F7CB4D"]Forecast[/TD]
[TD="bgcolor: #8BC34A"]Actual[/TD]
[TD="bgcolor: #E91D63"]Plan[/TD]
[TD="bgcolor: #F7CB4D"]Forecast[/TD]
[TD="bgcolor: #8BC34A"]Actual[/TD]
[TD="bgcolor: #E91D63"]Plan[/TD]
[TD="bgcolor: #F7CB4D"]Forecast[/TD]
[TD="bgcolor: #8BC34A"]Actual[/TD]
[TD="bgcolor: #E91D63"]Plan[/TD]
[TD="bgcolor: #F7CB4D"]Forecast[/TD]
[TD="bgcolor: #8BC34A"]Actual[/TD]
[TD="bgcolor: #E91D63"]Plan[/TD]
[TD="bgcolor: #F7CB4D"]Forecast[/TD]
[TD="bgcolor: #8BC34A"]Actual[/TD]
[TD="bgcolor: #E91D63"]Plan[/TD]
[TD="bgcolor: #F7CB4D"]Forecast[/TD]
[TD="bgcolor: #8BC34A"]Actual[/TD]
[/TR]
[TR]
[TD="align: center"]#VALUE![/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #000000"][/TD]
[TD="bgcolor: #000000"][/TD]
[TD="align: right"]1,746.00[/TD]
[TD][/TD]
[TD="align: right"]1,035.50[/TD]
[TD="align: right"]2,037.00[/TD]
[TD][/TD]
[TD="align: right"]3,375.50[/TD]
[TD="align: right"]2,037.00[/TD]
[TD][/TD]
[TD="align: right"]627.50[/TD]
[TD="align: right"]2,037.00[/TD]
[TD][/TD]
[TD="align: right"]2,553.75[/TD]
[TD="align: right"]2,932.39[/TD]
[TD][/TD]
[TD="align: right"]13,796.00[/TD]
[TD="align: right"]4,126.25[/TD]
[TD][/TD]
[TD="align: right"]6,026.00[/TD]
[/TR]
</tbody>[/TABLE]

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
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Sort by date Sort by votes
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:
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
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:
Upvote 0
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...
 
Upvote 0
Maybe something like this:


Book1
ABCDEFGHIJKLMNOPQRSTU
1YTD 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
212/31/18 - 12/29/19JanDec-3101-JunJan01-JulJan-13JanJan-14Jan-20JanJan-21Jan-27FebJan-2802-MarFeb02-Apr02-Oct
3PlanActualPlanForecastActualPlanForecastActualPlanForecastActualPlanForecastActualPlanForecastActualPlanForecastActual
41,746.001,035.502,037.003,375.502,037.00627.52,037.002,553.752,932.3913,796.004,126.256,026.00
5
6YTD Total27414.25
Sheet1
Cell Formulas
RangeFormula
C6=SUMPRODUCT(SUMIFS(D4:U4,D3:U3,"Actual",B1:S1,"FW "&TEXT(ROW(A1:INDEX(A:A,B1)),"00")))
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Solved
Validate month based on contract dates & Calculate number of weeks in that month to calculate hours booked per month
2
Replies
16
Views
2K
phiero21
P
J
  • Question Question
Need assistance with auto disaggregation of data from monthly to weekly
Replies
1
Views
475
jajatidev
J
M
  • Question Question
Help with FilterXML formula
Replies
0
Views
2K
montex01
M
C
  • Question Question
Need help with look up formula
Replies
5
Views
667
GuzmanServices
G
MrDB4Excel
  • Question Question
If Find Blank Cell
Replies
0
Views
710
MrDB4Excel
MrDB4Excel

Forum statistics

Threads
1,224,818
Messages
6,181,152
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top