Adding every other cells

j3andc

Board Regular
Joined
Mar 4, 2002
Messages
170
Is there a way to create a formula that will add up every other cell in a column or row?
other than by individually picking the cells.
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

ChrisUK

Well-known Member
Joined
Sep 3, 2002
Messages
675
Try this VBA code

Set rng = Range("A1:A20")
For Each c In rng
If c.Row Mod 2 = 0 Then
Cells(1, 2).Value = Cells(1, 2).Value + c.Value
End If
Next
 

IML

MrExcel MVP
Joined
Feb 15, 2002
Messages
1,743
A formula approach could be
=SUMPRODUCT((A1:A10)*(MOD(ROW(A1:A10),2)=0))

to add even rows in A1:A10.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
aaSumEveryOther j3andc.xls
ABCDEFGHI
10Mth
24965762Nth
317
4213
51
67
72
84
94
10
Sheet1


The formula

=SUMPRODUCT((MOD(COLUMN(A2:F2)-CELL("Col",$A2)+H1,H2)=0)*(A2:F2))

sums every Nth value in a horizontal range.

The formula

=SUMPRODUCT((MOD(ROW(A4:A9)-CELL("Row",$A4)+H1,H2)=0)*(A4:A9))

sums every Nth value in a vertical range.

H1-value is the parameter that indicates the position from which to start the summing.

H2-value indicates every Nth value to sum.

When H1 = 0 and H2 = 2, the following values

{4,0,6,0,7,0} in the horizontal range, and

{2;0;7;0;4;0} in the vertical range are summed.

When H1 = 1 and H2 = 2, the following values

{0,9,0,5,0,6} in the horizontal range and

{0;1;0;2;0;4} in the vertical range are summed.
This message was edited by Aladin Akyurek on 2002-09-12 10:00
 

Watch MrExcel Video

Forum statistics

Threads
1,118,730
Messages
5,573,870
Members
412,555
Latest member
mark84
Top