+ Reply to Thread
Results 1 to 7 of 7

Need a formula to sum a rolling three months based on input date

  1. #1
    Registered User
    Join Date
    07-10-2015
    Location
    Beaverton, Oregon
    MS-Off Ver
    Excel 2010
    Posts
    8

    Need a formula to sum a rolling three months based on input date

    "Hi there, I am trying to figure out how to create a consise formula that will sum a rolling 3 months of data based on a date input.

    I need to be able to input a date in B1 and have C4:C9 (should sum K4, I4 and G4) and D4:D9 return results for the month input in B1 and the prior two months.

    The results in C4 should = 8 and D4 should sum K3, I3, G3 divided by the result of the formula in C4.

    I have never posted to a forum, if I am not clear or have done something incorrectly please advise.

    Any assistance is much appreciated!"
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    02-22-2013
    Location
    London / Stockholm
    MS-Off Ver
    Office 365
    Posts
    1,218

    Re: Need a formula to sum a rolling three months based on input date

    Welcome to the forum!

    Try,

    C4: =SUMIFS($F4:$O4,$F$2:$O$2,">="&EDATE($B$1,-2),$F$2:$O$2,"<"&EDATE($B$1,1))

    Best,
    berlan

  3. #3
    Registered User
    Join Date
    07-10-2015
    Location
    Beaverton, Oregon
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Need a formula to sum a rolling three months based on input date

    Berlan, that's awesome! works perfect... I do also need something for the 3 month % in D4 -- D4 should sum K3, I3, G3 divided by the result of the formula in C4.

    Can you assist with that ?

    Thanks,
    Diane

  4. #4
    Registered User
    Join Date
    07-10-2015
    Location
    Beaverton, Oregon
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Need a formula to sum a rolling three months based on input date

    Berlan,

    Thank you so much!! I works perfect and I did get the second piece figured out.

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,998

    Re: Need a formula to sum a rolling three months based on input date

    Just out of curiosity, how did you do that?

    either =SUMIFS($F4:$O4,$F$2:$O$2,">="&EDATE($B$1,-2),$F$2:$O$2,"<"&EDATE($B$1,1))/SUMIFS($F$3:$O$3,$F$2:$O$2,">="&EDATE($B$1,-2),$F$2:$O$2,"<"&EDATE($B$1,1))
    or =SUMIFS($F4:$O4,$F$2:$O$2,">="&EDATE($B$1,-2),$F$2:$O$2,"<"&EDATE($B$1,1))/$C$3
    is what I would have used
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  6. #6
    Registered User
    Join Date
    07-10-2015
    Location
    Beaverton, Oregon
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Need a formula to sum a rolling three months based on input date

    FDibbins,
    I changed it ever so slightly and did...
    C4 =SUMIFS($E4:$O4,$E$2:$O$2,">="&EDATE($B$1,-2),$E$2:$O$2,"<"&EDATE($B$1,1))
    D3 =SUMIFS($E3:$O3,$E$2:$O$2,">="&EDATE($B$1,-2),$E$2:$O$2,"<"&EDATE($B$1,1))
    D4 =C4/D3

    I needed to sum the defects, the loan count and then come up with the % -- all to change based on the date I enter.

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,998

    Re: Need a formula to sum a rolling three months based on input date

    great job, thanks for sharing

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Rolling months based on date
    By Rob (SA) in forum Excel General
    Replies: 4
    Last Post: 11-13-2014, 02:35 AM
  2. [SOLVED] How to change cell value based on 12 months rolling date on other cell
    By buznac in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-31-2013, 07:30 PM
  3. Rolling sum of 12 months against each month based on two criteria
    By agupta5231 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-20-2013, 12:01 AM
  4. Formula for rolling back three months
    By stryped in forum Excel General
    Replies: 29
    Last Post: 12-21-2011, 12:39 PM
  5. Sum of Rolling 12 months formula
    By b_motl in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-23-2008, 09:07 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1