Formula to sum up a rolling 3 months

[Rupe]

Hi - I'm building a sales forecast sheet. I'd like a formula that will look forward and give me the sum of the next 3 months of sales target. I have a drop down field on the sheet, indicating the current month.

e.g. If my drop down on date is set to Feb 23, I'd like the formula to return the sum of the target for March 23, April 23 and May 23. If my drop down date field changes to March 23, I'd like the sum of the targets for April 23, May 23 and June 23.

May targets are arranged horizontally in a row, by month

Thanks!

[Glenn Kennedy]

Hi. Before we get going on this one...

Is your forum profile showing the Excel PRODUCT that you need this request to work with? CLEARLY, you are no longer using Excel 2003.

The best solutions often rely on knowing WHICH Office PRODUCT (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date. If you aren't sure, in Excel go to File/Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this.

The three most recent Excel PRODUCTS are Excel 2019, Excel 2021 and MS365 - if you are using MS365, please give this name along with the Version number in your profile (e.g. MS365 (PC) Version 2211). The version number is in the About Excel section further down the Account page.

Cheers,

Glenn.

[Glenn Kennedy]

One way:

=SUM(INDEX($B$4:$M$4,,MATCH($B$1,$B$3:$M$3,0)+1):INDEX($B$4:$M$4,,MATCH($B$1,$B$3:$M$3,0)+3))

[Croweater]

Another (for Excel 2003);

=SUM(INDIRECT(ADDRESS(4,MATCH($B$1,A3:M3,0))):OFFSET(INDIRECT(ADDRESS(4,MATCH($B$1,A3:M3,0))),0,2))

[Rupe]

Thank Glenn - will take a look at that formula and let you know if any other Qs

Yes have updated excel version in my profile!

[josephteh]

Another way, =SUMIFS(B4:M4,B3:M3,">"&B1,B3:M3,"<="&EDATE(B1,3))

[Glenn Kennedy]

With 2208, you should also be able to use this:

=SUM(TAKE(FILTER(B4:M4,B3:M3>B1),,3))

[Rupe]

Just the job. Thanks.