Sum the column values by shifting the summation range with the specified increment
There is the following task:
Column A contains several thousand numbers. It is necessary to enter the sum of ten cells of column A in each cell in column B, i.e.
B1 = СУММ(A1:A10)
B2 = СУММ(A11:A20)
B3 = СУММ(A21:A30)
, etc.
Can you tell me if this can be done by a formula in the table itself, and not by a macro?
2 answers
=СУММ(СМЕЩ($A$1:$A$10;(СТРОКА(A1)-1)*10;))
But I don't recommend it. СМЕЩ - the function is volatile (recalculated at any change on the sheet). This affects performance, often the presence of formulas with volatile functions causes braking, sometimes hanging for a long time.
It is better to be a little longer, but without such possible troubles:
=СУММ(ИНДЕКС(A:A;(СТРОКА(A1)-1)*10+1):ИНДЕКС(A:A;СТРОКА(A1)*10))
The first ИНДЕКС defines the beginning of the range, the second-its end.
To be fair, it should be clarified that formulas with dynamic range definition are also they are recalculated, but only when the workbook is opened (here we are talking about recalculation that does not depend on the input data).
Insert this formula into the B1 cell and do the autofill (double-click on the lower-right corner of the cell). At the end, there will be a lot of cells with zeros due to the fact that autofill will insert this formula to the end of the values of column A.
=SUMS(OFFSET ($A$1; (STRING() - 1) * 10; 0; 10; 1))