0

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.

1
  • 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.
    – Rubén
    Aug 9 '21 at 22:40
0

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.

5
  • Many thaks for this very clever suggestion - I will go ahead and test this now ...
    – Gary K
    Aug 12 '21 at 21:21
  • PERFECTION! You are marvelously helpful and I'm in your debt, Thanks ever so much!
    – Gary K
    Aug 13 '21 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.
    – Erik Tyler
    Aug 13 '21 at 19:02
  • I hit the green checkmark - is that how to do the "best answer" thing? Thanks!
    – Gary K
    Aug 15 '21 at 18:08
  • Yes, that did it. Thanks for following through.
    – Erik Tyler
    Aug 15 '21 at 19:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.