In Excel/Google Sheets I have found how to sum every N columns on websites such as https://exceljet.net/formula/fixed-value-every-n-columns, but the problem is, from what I can see is that it starts at N column each time. I need something that starts from column 1 and then counts every N columns. like the following:
I need to do this with a formula and not a script.
=SUM(FILTER(A2:J2, MOD(A2:J2, L2)=1))
then drag to other cells
or use this array version:
=INDEX(TRANSPOSE(MMULT(A2:J4,TRANSPOSE(COLUMN(A2:J4)^0 *
N(MOD(COLUMN(A2:J4), L2:L4)=1)))), ,1)
If you want the cells that were added to be automatically highlighted.
Conditional formatting used on A2:J:
=MOD(COLUMN(), $L2)=1
With Google-Sheets, try:
Formula in M2:
=SUM(QUERY(TRANSPOSE(A2:J2),"Skipping "&L2))
Or, a single dynamic array formula (without dragging):
=INDEX(MMULT(A2:J4*(MOD(COLUMN(A2:J4),L2:L4)=1),SEQUENCE(10,1,1,0)))
Or, more dynamic:
=INDEX(MMULT(A2:J4*(MOD(COLUMN(A2:J4),L2:L4)=1),SEQUENCE(COLUMNS(A2:J4),1,1,0)))
Note: The latter would also work in Excel with slight modifications.
Try this formula on column M:
=SUM((sumif(ArrayFormula(mod((COLUMN(B2:J2)-COLUMN(B2)+1),L2)),0,B2:J2))+A2)
Here's the result on Column M.
Just to break down the code sumif(ArrayFormula(mod((COLUMN(B2:J2)-COLUMN(B2)+1),L2)),0,B2:J2) does the actual calculation with the number of intervals set on Column L but take note that I started at the 2nd column so the range here does not include the first column. The result from this is at the Column O highlighted red as you can see in the screenshot.
At the Column M is the actual solution where I only added the first column by using SUM on top of the previous formula.
I hope my explanation is clear.
Just copy/drag the formula down to each row and it should work.
Reference: How to Sum Every Nth Row or Column in Google Sheets Using SUMIF