sum every four rows

[yankee428]

column of 100 rows

i need to sum every fourth line. for example

1
2
3
4 sum = 10
5
6
7
8 sum = 26

 

[colbymack]

Insert a column. In the fourth row of the new column, put a formula that sums the cell to the left of it and the three cells above that one.
In the new column, highlight the cell with the formula and the three above it. Select Edit Copy. Starting with the cell just below it, highlight all the way down to the last row. Selecte Edit Paste.

hth,
Colbymack

 

[Richard Schollar]

enter this:

=SUM(IF(MOD(ROW(A1:A100),4)=0,A1:A100))

and confirm with Ctrl+Shift+Enter (it's an array formula).

Best regards

Richard

 

[Aladin Akyurek]

enter this:

=SUM(IF(MOD(ROW(A1:A100),4)=0,A1:A100))

and confirm with Ctrl+Shift+Enter (it's an array formula).

Best regards

Richard

I don't believe that meets OP's question.

 

[vane0326]

A non-array formula.


Put this formula in cell A4 and copy it down.

=IF(ROUND((CELL("row",A4)/4),0)=(CELL("row",A4)/4),SUM(A1:OFFSET(A4,0,0)),"")

Sum every 4 rows.xls
	A	B	C	D
1	1
2	2
3	3
4	4	10
5	5
6	6
7	7
8	8	26
9	9
10	10
11	11
12	12	42
13	13
14	14
15	15
16	16	58
Sheet1

 

[Aladin Akyurek]

A non-array formula.


Put this formula in cell A4 and copy it down.

=IF(ROUND((CELL("row",A4)/4),0)=(CELL("row",A4)/4),SUM(A1:OFFSET(A4,0,0)),"")
...

Is that not too expensive and non-robust?

 

[vane0326]

A non-array formula.


Put this formula in cell A4 and copy it down.

=IF(ROUND((CELL("row",A4)/4),0)=(CELL("row",A4)/4),SUM(A1:OFFSET(A4,0,0)),"")
...

Is that not too expensive and non-robust?

Another way.


=IF(ROUND((CELL("row",A4)/4),0)=(CELL("row",A4)/4),SUM(A1:A4),"")


What do you think ?

 

[just_jon]

=IF(MOD(ROW(),4)=0,SUM(INDEX(A:A,ROW()-3):A1),"")

In A1 copied down.

 

[vane0326]

=IF(MOD(ROW(),4)=0,SUM(INDEX(A:A,ROW()-3):A1),"")

In A1 copied down.

I like that !



I would like to know how does this fit in your formula " : "

:A1

 

[Aladin Akyurek]

The procedure colbymack describes is the best thing to execute.