Forum Discussion
Solved
Using a formula to sum annual totals based on "Last Month" and "Year the Last Month is in"
I have monthly totals over multiple years that I need to sum trailing 12 month totals when given the "Last Month" and the "Year" the last month is in. For example, I have the "Months" as columns (starting with January and ending with December from left to right) and "Years" as rows (for the past 10 years starting with 2011 and ending at the bottom row with 2022) in a chart. When asked to sum up the monthly totals for the "Trailing 12 Months ending June 2022" I want to see if there is a formula to provide the sum of those month (July 2021 through June 2022) without doing it manually.
=SUM(OFFSET($B$2,MATCH(B16,$A$2:$A$13,0)-1,0,1,MATCH(C16,$B$1:$M$1,0)))+SUM(OFFSET($B$2,MATCH(B16,$A$2:$A$13,0)-2,MATCH(C16,$B$1:$M$1,0),1,13-MATCH(C16,$B$1:$M$1,0)))You can try this formula which returns the expected results in my sheet with the assumption that there are no entries in column N.
=SUM(OFFSET($B$2,MATCH(B16,$A$2:$A$13,0)-1,0,1,MATCH(C16,$B$1:$M$1,0)))+SUM(OFFSET($B$2,MATCH(B16,$A$2:$A$13,0)-2,MATCH(C16,$B$1:$M$1,0),1,13-MATCH(C16,$B$1:$M$1,0)))You can try this formula which returns the expected results in my sheet with the assumption that there are no entries in column N.
OliverScheurich thank you very much!!!!!
