Rolling Total from the last entered column

[srinidhi]

Hi,

A:L column has months like April'16, may 16..........April 17. As of now the data is filled only from April - July. I want the rolling total for 4 months. When the data is entered for Aug, the formula should sum up from May to Aug. When the data is entered for Sept the formula should sum up from June to Sept.

 

[bosco_yip]

Try……..

1] In A1, entered 01/04/2016 and drag right to M1

2] In range A2:D2, filled data for April16 - July16

3] In A3, ented formula :

=SUM(OFFSET(A2,,MATCH(9^9,2:2)-1,,-4))

4] When the data is entered for Aug, the formula should sum up from May to Aug.

5] When the data is entered for Sept the formula should sum up from June to Sept.

Regards
Bosco

 

[srinidhi]

@ Bosco, thanks for the quick reply, I want the formula in N2 Column. Your formula works for Rows, I want it column wise. My data is from A2:M500 & the formula should be in N2 to N500. Uploading a sample file. Thanks

 

[bosco_yip]

@ Bosco, thanks for the quick reply, I want the formula in N2 Column. Your formula works for Rows, I want it column wise. My data is from A2:M500 & the formula should be in N2 to N500. Uploading a sample file. Thanks

N2, copy down :

=SUM(OFFSET(A2,,MATCH(9.99E+307,A2:M2)-1,,-4))

Regards

 

[srinidhi]

N2, copy down :

=SUM(OFFSET(A2,,MATCH(9.99E+307,A2:M2)-1,,-4))

Regards

Thanks a ton Bosco