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

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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

A formula approach could be
=SUMPRODUCT((A1:A10)*(MOD(ROW(A1:A10),2)=0))

to add even rows in A1:A10.

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
1
Views
111
Replies
4
Views
243
Replies
0
Views
125
Replies
0
Views
87
Replies
4
Views
206

1,219,005
Messages
6,145,706
Members
450,635
Latest member
Rookie3510

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back