# sum every four rows

#### yankee428

##### Active Member
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

### Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

#### colbymack

##### Active Member
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

##### MrExcel MVP
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

##### MrExcel MVP
Parsnip said:
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

##### Well-known Member

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
ABCD
11
22
33
4410
55
66
77
8826
99
1010
1111
121242
1313
1414
1515
161658
Sheet1

##### MrExcel MVP
vane0326 said:
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

##### Well-known Member

vane0326 said:
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

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

In A1 copied down.

#### vane0326

##### Well-known Member
just_jon said:
=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

##### MrExcel MVP
The procedure colbymack describes is the best thing to execute.

Replies
7
Views
64
Replies
3
Views
58
Replies
25
Views
194
Replies
4
Views
60
Replies
8
Views
138