Rolling 12 month based on cell value

Gman

Member
Hello I have a table similar to below that displays actual and forecasted numbers. I need to sum the most recent 12 months of actual's. I believe I will be using the offset function but cant figure out how to get it to start with the furthest right actual column?

=Sum(OFFSET(\$A\$4,0,(how do i get furthest right cell with "Actual"??)-12,1,12))

 2019.Jan 2019.Feb 2019.Mar 2019.Apr 2019.May 2019.Jun Actual Actual Actual AprFcst AprFcst AprFcst 100 200 200 150 150 150

my file has the previous 12 months of data, the table above wouldn't let me make it large enough. In this case I would need to sum 2019.Mar and before.

Thank you for any help,
Greg

navic

Active Member
If only the "Actual" header is required try this ARRAY formula below. (There are shorter but this school formula is from me.)
Code:
=SUM(INDIRECT(CHAR(64+(MATCH("Actual",\$A\$2:\$AA\$2,0)+(COUNTIF(A\$2:AA\$2,"Actual"))-12))&ROW(A4)&":"&CHAR(64+(MATCH("Actual",\$A\$2:\$AA\$2,0)+(COUNTIF(A\$2:AA\$2,"Actual"))-1))&ROW(A4)))
Put the formula in Row A4 at the end of the table.
The formula will sum up all values from Row 4 in last 12 columns (which have the title heading "Actual" in Row 2).
Hope it helped.

Attachments

• 9.5 KB Views: 12
• Gman

Gman

Member
Thank you. Works great.

Peter Bartholomew

Well-Known Member
I quite like the idea of narrowing down the date interval that you wish to report but there is another strategy available through the use of ...IFS functions. The start of the 12 month period could be calculated using
= EOMONTH( MAXIFS( date, numberType, "Actual"), -12 )
and the total of the actuals would then be given by
= SUMIFS( numbers, numberType, "actual", date, ">"&startDate )

Running through the entire time line may appear inefficient but SUMIFS are very fast (about 3x the speed of the corresponding array formula) so you would need many years data before you show any gain from the down-selection of ranges and, at the same time, avoiding volatile functions such as INDIRECT or OFFSET is a definite advantage.

Gman

Member
Thanks for the info Peter!

rahulshewale1

Active Member
hiii @Gman ,

Try below formula is also working,

CELL: AC4

Code:
=SUMIFS(\$A\$4:\$AA\$4,\$A\$1:\$AA\$1,">="&EOMONTH(INDEX(\$A\$1:\$AA\$1,AGGREGATE(14,6,(COLUMN(\$A\$1:\$AA\$1)-COLUMN(\$A\$1)+1)/(\$A\$2:\$AA\$2=\$X\$2),1)),-12)+1,\$A\$1:\$AA\$1,"<="&INDEX(\$A\$1:\$AA\$1,AGGREGATE(14,6,(COLUMN(\$A\$1:\$AA\$1)-COLUMN(\$A\$1)+1)/(\$A\$2:\$AA\$2=\$X\$2),1)),\$A\$2:\$AA\$2,"Actual")

Regard,
Rahul shewale