CODEWITHSUNDEEP
excelexcel-formula
user3243499
user3243499

Reputation: 3161

How to add rows in consecutive blocks in excel?

I have a sample excel column as shown below:

enter image description here

The above image is a resemblance of a single column long table.

What I want to do is: to calculate blockwise summation of consecutive blocks as shown by colors of block size 3. Currently, I am manually going to the top row of each block and using =SUM(A<row_num>:A<row_num+3>). But this is tedious for the very long column. Is there an automated way to calculate those formulas in excel?

Upvotes: 1

Views: 576

Answers (3)

SJR
SJR

Reputation: 23081

If the ranges are all 3 rows you could put this in A1

=SUM(OFFSET($A$1,3*(ROW()-1),0,3,1))

but the non-volatile approach is better.

Upvotes: 1

JvdV
JvdV

Reputation: 75900

You could use this formula in B1 and drag down:

1) Before edit - with whitespaces between sums:

=IF(MOD(ROW()+2,3)=0,SUM(INDEX(A:A,ROW()):INDEX(A:A,ROW()+2)),"")

2) After edit - without whitespaces between sums:

=SUM(INDEX(A:A,(ROW()*3)-2):INDEX(A:A,ROW()*3))

enter image description here

Upvotes: 3

basic
basic

Reputation: 11968

Non-volatile option:

=IFERROR(SUM(INDEX(A:A,N(IF(1,ROW()+{0;1;2}))))/((MOD(ROW(),3)=0)+(ROW()=1)),"")

enter image description here

Upvotes: 1

Related Questions