# Adding alternate numbers in a column

#### Toni

##### New Member
I have a worksheet with over 300 rows and 12 columns. I need to get the sum of every other number in each column. In other words, I need the system to add every other number and put it in the total box at the bottom of the work sheet. HELP!

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Book1
ABCDEFGH
1
2
323232323
4
5ForevenColumn8
6foroddColumn12
Sheet2

Toni said:
I have a worksheet with over 300 rows and 12 columns. I need to get the sum of every other number in each column. In other words, I need the system to add every other number and put it in the total box at the bottom of the work sheet. HELP!
Book2
ABCDEFG
26216121014
324181914823
4172018141319
524242012712
622101518723
710132217821
821241081012
961224191511
106191012188
1162424141324
12191218181617
13Every2ndTotals9110677827493
147091109765191
15
Sheet1

B13, copied across:

=SUMPRODUCT(--(MOD(ROW(B\$2:B\$12)-CELL("Row",B\$2)+0,2)=0),B\$2:B\$12)

Calculates a total of every 2nd value in a column range, starting with the first value in the range.

This is probably what you're after.

B14, copied across:

=SUMPRODUCT(--(MOD(ROW(B\$2:B\$12)-CELL("Row",B\$2)+1,2)=0),B\$2:B\$12)

Calculates a total of every 2nd value, starting with the first 2nd value in the range.

I was tryin to use DSUM to solve this. It appears to work as long as the criteria formula references the same row where you have the DSUM formula.

Any thoughts?
Book1.xls
ABCD
1List
211
3210
41
52
61
72
81
92
101
112
121
Sheet2

Nice idea but I get an error inthe formula:
Book2
ABCD
1ListAlternateMultiplyFormula
21110
3200.
4313.
5400.
6515.
7600.
8717.
9800.
10919.
111000.
12..2524
Sheet1

inarbeth said:
Nice idea but I get an error inthe formula: ...

Formulas do not show. So which nice idea is failing?

Sorry, they showed before, in my exhibit:

C2: =MOD(ROW(A3),2)
C3: =DSUM(A2:A17,1,C1:C2)

fairwinds said:
...

I was tryin to use DSUM to solve this. It appears to work as long as the criteria formula references the same row where you have the DSUM formula.

Any thoughts?

=MOD(ROW(A3),2)

=DSUM(A2:A17,1,C1:C2)

...

I had to fish up the DSUM formula from the HTML code itself. The exhibit fails to show it. Don't forget to repeat the formulas outside the exhibit, wil you?

Interesting idea...

I tried the MOD bits of the formulas I posted in the criteria range. DSUM appears to process them correctly...
aaTotalEvery2ndValue Toni.xls
ABCDE
1X
26TRUEFALSE
3249170
417
524
622
710
821
96
106
116
1219
Sheet2

Criteria range:

D1 left empty.

D2:

=MOD(ROW(A2)-CELL("Row",A\$2)+0,2)=0

D3:

=DSUM(\$A\$1:\$A\$12,1,\$D\$1:\$D\$2)

This totals every 2nd value starting with the first value in the range.

Criteria range:

E1 left empty.

E2:

=MOD(ROW(A2)-CELL("Row",A\$2)+1,2)=0

E3:

=DSUM(\$A\$1:\$A\$12,1,\$E\$1:\$E\$2)

Thanks!

After adding the CELL("Row",A\$2) it seems to behave concistantly.

inarbeth said:
Nice idea but I get an error inthe formula: ...

Formulas do not show. So which nice idea is failing?

Sorry I had a problem with the html maker and then had to do something else. You seem to have cracked it

Replies
4
Views
289
Replies
0
Views
205
Replies
11
Views
219
Replies
15
Views
379
Replies
3
Views
327

1,217,257
Messages
6,135,503
Members
449,945
Latest member
noone12344444444

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