Dynamic SUM Range

K

kivikatz

New Member
Joined
Sep 12, 2020
Messages
27
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Hi All. I want to SUM a 12 month range of numbers starting with the first month that has a number >0, and to start a new SUM range from the month following the previous SUM range.

For instance in the calendar year 2023, if April is the first month with a number >0, that would be the starting point of the SUM calculation (April - March), with the next 12 month period starting April 2024. If June is the first month with a number >0, that would be the starting month (June - May) with the next 12 month period starting in Jun 2024. And so on...

I've tried figuring this out but no luck. Attached is a sample Excel sheet. Hope you understand what I am trying to do. Thanks!

SUM Calculation.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJ
1Jan 2023Feb 2023Mar 2023Apr 2023May 2023Jun 2023Jul 2023Aug 2023Sep 2023Oct 2023Nov 2023Dec 2023Jan 2024Feb 2024Mar 2024Apr 2024May 2024Jun 2024Jul 2024Aug 2024Sep 2024Oct 2024Nov 2024Dec 2024Jan 2025Feb 2025Mar 2025Apr 2025May 2025Jun 2025Jul 2025Aug 2025Sep 2025Oct 2025Nov 2025Dec 2025
2000100100100100100100200200200200200200300300300300300300400400400400400400500500500500500500600600600
3Month start of SUM calculationMonth start of new SUM calculationMonth start of new SUM calculation
Sheet1
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Sort by date Sort by votes
See if this works for you:

Book1
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJ
11/01/20231/02/20231/03/20231/04/20231/05/20231/06/20231/07/20231/08/20231/09/20231/10/20231/11/20231/12/20231/01/20241/02/20241/03/20241/04/20241/05/20241/06/20241/07/20241/08/20241/09/20241/10/20241/11/20241/12/20241/01/20251/02/20251/03/20251/04/20251/05/20251/06/20251/07/20251/08/20251/09/20251/10/20251/11/20251/12/2025
2000100100100100100100200200200200200200300300300300300300400400400400400400500500500500500500600600600
3Month start of SUM calculationMonth start of new SUM calculationMonth start of new SUM calculation
4
5
61st 12 mths1800
72nd 12 mths4200
Sheet1
Cell Formulas
RangeFormula
B6B6=LET(firstCol, XMATCH(TRUE,2:2<>0), SUM(INDEX(2:2, firstCol) : INDEX(2:2, firstCol + 11)))
B7B7=LET(firstCol, XMATCH(TRUE,2:2<>0) + 12, SUM(INDEX(2:2, firstCol) : INDEX(2:2, firstCol + 11)))
 
Upvote 0
Alex Blakenburg said:
See if this works for you:

Book1
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJ
11/01/20231/02/20231/03/20231/04/20231/05/20231/06/20231/07/20231/08/20231/09/20231/10/20231/11/20231/12/20231/01/20241/02/20241/03/20241/04/20241/05/20241/06/20241/07/20241/08/20241/09/20241/10/20241/11/20241/12/20241/01/20251/02/20251/03/20251/04/20251/05/20251/06/20251/07/20251/08/20251/09/20251/10/20251/11/20251/12/2025
2000100100100100100100200200200200200200300300300300300300400400400400400400500500500500500500600600600
3Month start of SUM calculationMonth start of new SUM calculationMonth start of new SUM calculation
4
5
61st 12 mths1800
72nd 12 mths4200
Sheet1
Cell Formulas
RangeFormula
B6B6=LET(firstCol, XMATCH(TRUE,2:2<>0), SUM(INDEX(2:2, firstCol) : INDEX(2:2, firstCol + 11)))
B7B7=LET(firstCol, XMATCH(TRUE,2:2<>0) + 12, SUM(INDEX(2:2, firstCol) : INDEX(2:2, firstCol + 11)))
Click to expand...
Hi Alex. Thanks. That works out. Can you help me with another formula. I want to add a monthly running total for each year, that would reset at the beginning of each year. Here is a sample worksheet that illustrates want I am trying to do. Much appreciated.

SUM Calculation.xlsx
CDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABBBCBDBEBFBGBHBIBJ
1Jan 2023Feb 2023Mar 2023Apr 2023May 2023Jun 2023Jul 2023Aug 2023Sep 2023Oct 2023Nov 2023Dec 2023Jan 2024Feb 2024Mar 2024Apr 2024May 2024Jun 2024Jul 2024Aug 2024Sep 2024Oct 2024Nov 2024Dec 2024Jan 2025Feb 2025Mar 2025Apr 2025May 2025Jun 2025Jul 2025Aug 2025Sep 2025Oct 2025Nov 2025Dec 2025Jan 2026Feb 2026Mar 2026Apr 2026May 2026Jun 2026Jul 2026Aug 2026Sep 2026Oct 2026Nov 2026Dec 2026Jan 2027Feb 2027Mar 2027Apr 2027May 2027Jun 2027Jul 2027Aug 2027Sep 2027Oct 2027Nov 2027Dec 2027
2000100100100100100100100100100100100100200200200200200200200200200200200200300300300300300300300300300300300300400400400400400400400400400400400400500500500500500500500500500
310020030040050060070080090010001100120020040060080010001200140016001800200022002400300600900120015001800210024002700300033003600400800120016002000240028003200360040004400480050010001500200025003000350040004500
4Start YearStart YearStart YearStart YearStart Year
Sheet1
Cell Formulas
RangeFormula
F3:Q3F3=SUM($F2:F2)
R3:AC3R3=SUM($R2:R2)
AD3:AO3AD3=SUM($AD2:AD2)
AP3:BA3AP3=SUM($AP2:AP2)
BB3:BJ3BB3=SUM($BB2:BB2)
 
Upvote 0
Try this is C3 and copy it across:

Excel Formula: 
=LET(firstCol, XMATCH(TRUE, 2:2 <> 0),
         thisCol, COLUMN(),
         YrCnt, INT( (thisCol - firstCol) / 12),
         fromCell, INDEX(2:2, firstCol + (YrCnt * 12) ),
         toCell, INDEX(2:2, thisCol),
         IF(thisCol < firstCol, 0, SUM(fromCell : toCell)))
 
Upvote 0
Solution
Alex Blakenburg said:
Try this is C3 and copy it across:

Excel Formula: 
=LET(firstCol, XMATCH(TRUE, 2:2 <> 0),
         thisCol, COLUMN(),
         YrCnt, INT( (thisCol - firstCol) / 12),
         fromCell, INDEX(2:2, firstCol + (YrCnt * 12) ),
         toCell, INDEX(2:2, thisCol),
         IF(thisCol < firstCol, 0, SUM(fromCell : toCell)))
Click to expand...
Thanks! That really works well.
 
Upvote 0
Hi Alex. Thanks for all your help. I just have one issue that is cropping up with the formula (which I revised slightly). When I close the spreadsheet and open it up again, I get #VALUE! errors all the cells that use the formula. I've attached a sample to show you the problem. If I manually click into either Cell 1A/1B and either 4A/4B and press enter, it fixes the issues. This error crops up whether the cells in column A/B are blank, contain numbers or has text. I also formatted the cell as numbers. Not sure what is happening, but appreciate any help. Thanks.

Formulas.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABBBCBDBEBFBGBHBIBJ
1000100,000123,008151,309186,121238,943294,426362,816447,125551,064650,876768,767908,0121,072,4791,266,7381,496,1851,767,1942,087,2942,465,3782,911,9503,439,4184,062,4354,446,5364,867,0265,327,3615,831,3256,383,0626,987,1157,648,4538,372,5269,165,29710,033,30310,983,70212,024,33612,683,20213,378,35514,111,80614,885,67815,702,21316,563,77917,472,87518,432,14119,444,36520,512,49121,639,62722,829,05623,482,69624,155,18124,847,05825,558,89226,291,26227,044,76727,820,02228,617,66129,438,33530,282,71531,151,49432,045,381
2Running Total000#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!
3
4New00010,0001,8922,2502,67613,1826,3167,6739,32711,3469,89911,41713,16815,18817,51920,20823,31026,88931,01935,78441,28247,62728,86831,25533,84436,65239,69943,00646,59450,49054,71959,31064,29769,71241,77743,74045,79747,95350,21452,58555,07057,67660,40963,27566,28069,43238,62339,60440,61141,64442,70543,79344,91046,05647,23248,44049,67850,950
5Running Total#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!
Sheet1
Cell Formulas
RangeFormula
C2:BJ2,C5:BJ5C2=@LET(firstCol, XMATCH(TRUE, 1:1 <> 0),thisCol, COLUMN(),YrCnt, INT( (thisCol - firstCol) / 12),fromCell, INDEX(1:1, firstCol + (YrCnt * 12) ),toCell, INDEX(1:1, thisCol), IF(thisCol < firstCol, 0, SUM(fromCell : toCell)))

Alex Blakenburg said:
Thanks for letting me know. Glad I could help.
Click to expand...
 
Upvote 0
kivikatz said:
When I close the spreadsheet and open it up again, I get #VALUE! errors all the cells that use the formula.
Click to expand...
Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: #value!
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 
Upvote 0
W
Peter_SSs said:
Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: #value!
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
Click to expand...
Well noted. I did mention when I posted on the other site that I had previously posted the question here. I guess I should have added a link to this post, as well as update here that I reposted. Thanks for the guidance. I will add links if I ever repost in the future (this was my first time).
 
Upvote 0
kivikatz said:
I guess I should have added a link to this post, as well as update here that I reposted.
Click to expand...
Yes, that's the right way to go. Once you have the same question in multiple forums, make sure all forums have links to all the others. Cheers.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Sum across multiple sheets based on date
Replies
9
Views
131
HighAndWilder
H
J
  • Question Question
How to create monthly report for activities based on created date, closed date, and due date, for past year?
Replies
1
Views
450
josevaldesv1
J
S
  • Question Question
SUM and CHOOSE formula question
Replies
7
Views
240
Fluff
Fluff
C
  • Question Question
Continuous Exponential Growth Curve Across Phases
Replies
1
Views
251
FormulasOnly
F
S
  • Solved
Count of active employees between dates.
Replies
3
Views
667
Fluff
Fluff

Forum statistics

Threads
1,223,911
Messages
6,175,327
Members
452,635
Latest member
laura12345

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