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!
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 |