#### j3andc

##### Board Regular
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.

### 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
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
A formula approach could be
=SUMPRODUCT((A1:A10)*(MOD(ROW(A1:A10),2)=0))

to add even rows in A1:A10.

#### lenze

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

Replies
4
Views
52
Replies
7
Views
65
Replies
1
Views
104
Replies
10
Views
151
Replies
7
Views
144