Adding alternate numbers in a column

Toni

New Member
Joined
Jan 18, 2005
Messages
1
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

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.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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.
 

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
Hi Aladin.

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
 

inarbeth

Well-known Member
Joined
Apr 8, 2002
Messages
912

ADVERTISEMENT

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
 

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638

ADVERTISEMENT

Sorry, they showed before, in my exhibit:

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

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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)
 

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
Thanks!

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

inarbeth

Well-known Member
Joined
Apr 8, 2002
Messages
912
Aladin Akyurek said:
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 :biggrin:
 

Forum statistics

Threads
1,148,529
Messages
5,747,230
Members
424,070
Latest member
smanni3

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top