• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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.Jan2019.Feb2019.Mar2019.Apr2019.May2019.Jun
ActualActualActualAprFcstAprFcstAprFcst
100200200150150150

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

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

bosco_yip

Excel Ninja
61246

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
 

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.
 

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
 
Top