Rolling 12 month based on cell value

[Gman]

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]

If only the "Actual" header is required try this ARRAY formula below. (There are shorter but this school formula is from me.)

=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.

 

[bosco_yip]

Assume your data put in A1:F4 as per above picture

In B7, enter formula :

=SUM(OFFSET($A$4,0,COUNTIF(2:2,"Actual")-1,1,-MIN(COUNTIF(2:2,"Actual"),12)))

Regards
Bosco

 

[Gman]

Thank you. Works great.

 

[Peter Bartholomew]

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]

Thanks for the info Peter!

 

[rahulshewale1]

hiii @Gman ,

Try below formula is also working,

CELL: AC4

=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