Can Excel autofill a formula with a specific interval?-CodePudding

I am trying to autofill an excel formula for a cumulative sum of daily data.

Each day, there is a value recorded. Every 14th day, I need the sum of the previous 14 days.

I am pulling data from a table that has a value for each day and using the data in another table with rows only every 14 days.

Example Table One:

Date     Value
1/1      1
1/2      1
1/3      1
1/4      1
1/5      1
1/6      1
1/7      1
1/8      1
1/9      1
1/10     1
1/11     1
1/12     1
1/13     1
1/14     1
1/15     1
1/16     1
1/17     1
1/18     1
1/19     1
1/20     1
1/21     1
1/22     1
1/23     1
1/24     1
1/25     1
1/26     1
1/27     1
1/28     1
1/29     1
1/30     1
1/31     1
2/1      1
2/2      1
2/3      1
2/4      1
2/5      1
2/6      1
2/7      1
2/8      1
2/9      1
2/10     1
2/11     1

Example Table Two:

Date     Cumulative Value     (Desired Auto-fill Formula)       (Real Auto-fill Formula)
1/14     14                   =SUM(ExampleTableOne!$B$2:$B15)         =SUM(ExampleTableOne!$B$2:$B15)
1/28     28                   =SUM(ExampleTableOne!$B$2:$B29)         =SUM(ExampleTableOne!$B$2:$B16)
2/11     42                   =SUM(ExampleTableOne!$B$2:$B43)         =SUM(ExampleTableOne!$B$2:$B17)

Basically, I would like to know if there is a way in Excel to have the autofill function recognize that the upper limit SUM() value has an interval of 14 rows for every 1 row instead of 1.

CodePudding user response：

Use SUMIFS:

=SUMIFS(B:B,A:A,"<="&D2)

If you really want to use a dynamic range then use INDEX:

=SUM($B$2:INDEX(B:B,MATCH(D2,A:A,0)))

Now if you just want to jump 14 rows regardless of the date in column D then use:

=SUM($B$2:INDEX(B:B,(ROW($ZZ1)-1)*14 15))