Dynamic SUM Range

[kivikatz]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ
1	Jan 2023	Feb 2023	Mar 2023	Apr 2023	May 2023	Jun 2023	Jul 2023	Aug 2023	Sep 2023	Oct 2023	Nov 2023	Dec 2023	Jan 2024	Feb 2024	Mar 2024	Apr 2024	May 2024	Jun 2024	Jul 2024	Aug 2024	Sep 2024	Oct 2024	Nov 2024	Dec 2024	Jan 2025	Feb 2025	Mar 2025	Apr 2025	May 2025	Jun 2025	Jul 2025	Aug 2025	Sep 2025	Oct 2025	Nov 2025	Dec 2025
2	0	0	0	100	100	100	100	100	100	200	200	200	200	200	200	300	300	300	300	300	300	400	400	400	400	400	400	500	500	500	500	500	500	600	600	600
3				Month start of SUM calculation												Month start of new SUM calculation												Month start of new SUM calculation
Sheet1

 

[kivikatz]

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.

 

[Peter_SSs]

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.