sum every four rows

yankee428

Active Member
Joined
Apr 12, 2004
Messages
348
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
 

Some videos you may like

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
Joined
Jul 14, 2005
Messages
333
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
Joined
Apr 19, 2005
Messages
23,707
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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
Joined
Aug 29, 2004
Messages
819

ADVERTISEMENT

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
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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
Joined
Aug 29, 2004
Messages
819

ADVERTISEMENT

Aladin Akyurek said:
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
Joined
Sep 3, 2002
Messages
10,473
=IF(MOD(ROW(),4)=0,SUM(INDEX(A:A,ROW()-3):A1),"")

In A1 copied down.
 

vane0326

Well-known Member
Joined
Aug 29, 2004
Messages
819
just_jon said:
=IF(MOD(ROW(),4)=0,SUM(INDEX(A:A,ROW()-3):A1),"")

In A1 copied down.


I like that ! :biggrin:



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

:A1
 

Watch MrExcel Video

Forum statistics

Threads
1,118,291
Messages
5,571,331
Members
412,382
Latest member
Langtn02
Top