# 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!"  Register To Reply

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  Register To Reply

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  Register To Reply

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.  Register To Reply

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  Register To Reply

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.  Register To Reply

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

great job, thanks for sharing   Register To Reply