Dynamic SUM Range

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
 
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.
Will do. I found out the problem with the formula. Apparently, the version of Excel I’m using (Mac) does not support Let functions, which is why the errors cropped up. When I switch to Windows-based Excel (via Parallels) on my MacBook it works.

Thanks for the guidance. Will make sure my etiquette is right next time.
 
Upvote 0

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Apparently, the version of Excel I’m using (Mac) does not support Let functions,
I don't have a Mac to check, but I'm thinking that perhaps you just need to ensure you have the latest updates installed?
  • According to the M/Soft Help on LET it is available in 365 for Mac, and
  • This user (356 on Mac) seemed very happy with the LET formula in the previous post.
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,637
Latest member
Ezio2866

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