Adding alternate numbers in a column

[Toni]

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!

 

[Chitosunday]

Book1
	A	B	C	D	E	F	G	H
1
2
3	2	3	2	3	2	3	2	3
4
5	ForevenColumn	8
6	foroddColumn	12
Sheet2

 

[Aladin Akyurek]

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
	A	B	C	D	E	F	G
2		6	21	6	12	10	14
3		24	18	19	14	8	23
4		17	20	18	14	13	19
5		24	24	20	12	7	12
6		22	10	15	18	7	23
7		10	13	22	17	8	21
8		21	24	10	8	10	12
9		6	12	24	19	15	11
10		6	19	10	12	18	8
11		6	24	24	14	13	24
12		19	12	18	18	16	17
13	Every2ndTotals	91	106	77	82	74	93
14		70	91	109	76	51	91
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]

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
	A	B	C	D
1	List
2	1		1
3	2		10
4	1
5	2
6	1
7	2
8	1
9	2
10	1
11	2
12	1
Sheet2

 

[inarbeth]

Nice idea but I get an error inthe formula:

Book2
	A	B	C	D
1	List	Alternate	Multiply	Formula
2	1	1	1	0
3	2	0	0	.
4	3	1	3	.
5	4	0	0	.
6	5	1	5	.
7	6	0	0	.
8	7	1	7	.
9	8	0	0	.
10	9	1	9	.
11	10	0	0	.
12	.	.	25	24
Sheet1

 

[Aladin Akyurek]

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

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

 

[fairwinds]

Sorry, they showed before, in my exhibit:

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

 

[Aladin Akyurek]

...

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
	A	B	C	D	E
1	X
2	6			TRUE	FALSE
3	24			91	70
4	17
5	24
6	22
7	10
8	21
9	6
10	6
11	6
12	19
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]

Thanks!

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

 

[inarbeth]

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