# Sum the current row plus the next 6 (7 total)

I have a large spreadsheet and I want to sum the current row + the next 6 rows in a computed column (in this case, column O). So if I'm on row 21, for example, I want to sum column G from G21 to G27. Then on row 22, I want to sum column G from G22 to G28, etc. etc. I can do this easily with a formula in each cell, but I want to set a single ARRAY statement at the top and let it do all the work in each subsequent row. I've got this working beautifully with rankings and other kinds of calculations, but I'm stymied as to this one. Can you make any recommendations?

Separately, I'd also like to extrapolate in similar fashion the 7 day average in a separate column (column P). I'm hoping that the same ARRAY formula will be useable for this as well.

Not sure if it's relevant or not, but I'm using the standard model of `arrayformula` coupled with an `isblank` statement to stop processing at the last row. This is data imported using `importjson`, so the number of rows varies.

• Welcome to Web Applications. Please add some sample source data, the corresponding expected result, show what you tried (add the formula) and a brief description of your search efforts as is suggested in How to Ask. Aug 9, 2021 at 22:40

Suppose that your Col-G numbers run G2:G.

Place the following formula in O2:

`=ArrayFormula(IF(G2:G="",,SUMIF(ROW(G2:G),"<"&ROW(G2:G)+7,G2:G)-SUMIF(ROW(G2:G),"<"&ROW(G2:G),G2:G)))`

Then place the following formula in P2:

`=ArrayFormula(IF(G2:G="",,O2:O / COUNTIFS(G2:G,"<>",ROW(G2:G),"<"&ROW(G2:G)+7,ROW(G2:G),">="&ROW(G2:G))))`

As for the Col-O formula, we cannot use `SUMIFS` as one might expect with `ArrayFormula( )`. That is because `SUMIFS` itself relies on complex arrays in its functionality, and we'd essentially be asking Sheets to do a 3D array, which it cannot, strictly speaking.

Instead, we simply get a `SUMIF` of of all numbers in rows less than the current row number +7 and then subtract a second `SUMIF` of any rows with row numbers less than the current row. This results in the target range of seven rows added per row.

We need the somewhat complex Col-P formula to act on the Col-O formula because your final rows will not be divided by a uniform 7, but rather by however many rows had numbers in Col-G. For instance, the penultimate data row must be divided by 2, not 7; and the final row by 1, not 7.

The functionality of the `COUNTIFS` is similar to what was done with the two `SUMIF` formulas. However, `COUNTIFS` does work with `ArrayFormula`; so it can be constructed with all elements of the separate `SUMIF` formulas as its parameters.

• Many thaks for this very clever suggestion - I will go ahead and test this now ... Aug 12, 2021 at 21:21
• PERFECTION! You are marvelously helpful and I'm in your debt, Thanks ever so much! Aug 13, 2021 at 15:53
• Glad it all worked out. If you would please take a moment to mark the above post as "Best Answer." This allows the contributor community to see at a glance from the main dashboard which issues have already been fully resolved; and it allows future site visitors to most quickly find relevant answers. Aug 13, 2021 at 19:02
• I hit the green checkmark - is that how to do the "best answer" thing? Thanks! Aug 15, 2021 at 18:08
• Yes, that did it. Thanks for following through. Aug 15, 2021 at 19:14