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

1. ## 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!"

2. ## 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. ## 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. ## 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. ## 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

6. ## 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. ## Re: Need a formula to sum a rolling three months based on input date

great job, thanks for sharing

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

#### 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