Excell 2000 worksheet
 

how may I devise a formula for adding only alternate cells in columns?

Even rows: =SUMPRODUCT(--(MOD(ROW(B1:B30),2)=0),B1:B30)
Odd rows; =SUMPRODUCT(--(MOD(ROW(B1:B30),2)=1),B1:B30)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"KRM" wrote in message
...
how may I devise a formula for adding only alternate cells in columns?

"Bob Phillips" wrote...
Even rows: =SUMPRODUCT(--(MOD(ROW(B1:B30),2)=0),B1:B30)
Odd rows; =SUMPRODUCT(--(MOD(ROW(B1:B30),2)=1),B1:B30)

Yes, but if you had the sum of even rows in, say, X99, then it'd be more
efficient to calculate the sum of odd rows using

=SUM(B1:B30)-X99

True, but he asked for summing alternate rows without saying whether he
wanted odd or even, so I gave him both. He did not say he wanted two sums,.

Bob

"Harlan Grove" wrote in message
...
"Bob Phillips" wrote...
Even rows: =SUMPRODUCT(--(MOD(ROW(B1:B30),2)=0),B1:B30)
Odd rows; =SUMPRODUCT(--(MOD(ROW(B1:B30),2)=1),B1:B30)

Yes, but if you had the sum of even rows in, say, X99, then it'd be more
efficient to calculate the sum of odd rows using

=SUM(B1:B30)-X99

KRM wrote:
how may I devise a formula for adding only alternate cells in columns?

=SUMPRODUCT(--(MOD(ROW(A3:A8)-ROW($A$3)+0,2)=0),A3:A8)

Replace the +0 bit with +1 if summing must start with the first 2nd value.

Note that 2 (the Nth row parameter) can be any admissible value, odd or
even.

I'm grateful to you for this help. I'd never have got it by myself. Thanks,
KRM

"Harlan Grove" wrote:

"Bob Phillips" wrote...
Even rows: =SUMPRODUCT(--(MOD(ROW(B1:B30),2)=0),B1:B30)
Odd rows; =SUMPRODUCT(--(MOD(ROW(B1:B30),2)=1),B1:B30)

Yes, but if you had the sum of even rows in, say, X99, then it'd be more
efficient to calculate the sum of odd rows using

=SUM(B1:B30)-X99

I'm grateful to you for this help. I'd never have got it by myself. Thanks,
KRM

"Bob Phillips" wrote:

Even rows: =SUMPRODUCT(--(MOD(ROW(B1:B30),2)=0),B1:B30)
Odd rows; =SUMPRODUCT(--(MOD(ROW(B1:B30),2)=1),B1:B30)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"KRM" wrote in message
...
how may I devise a formula for adding only alternate cells in columns?

I'll bet that's a great solution but it's like a PHD coaching a
kindergardener. Many thanks anyway. KRM

"Aladin Akyurek" wrote:

KRM wrote:
how may I devise a formula for adding only alternate cells in columns?

=SUMPRODUCT(--(MOD(ROW(A3:A8)-ROW($A$3)+0,2)=0),A3:A8)

Replace the +0 bit with +1 if summing must start with the first 2nd value.

Note that 2 (the Nth row parameter) can be any admissible value, odd or
even.