Help in Excel 2000
 

How do I get sum of all odd or even numbers from the series (1, 2, 3, .....,
n) by using SUMIF function and MOD function or any other function?

One way:

Assuming series / numbers is in row1

Sum of Even:
=SUMPRODUCT(--(MOD((1:1),2)=0),1:1)

Sum of Odd:
=SUMPRODUCT(--(MOD((1:1),2)=1),1:1)

If there could be possibly some "text" numbers in row1,
try:

Sum of Even:
=SUMPRODUCT(--(MOD((1:1),2)=0),--(1:1))

Sum of Odd:
=SUMPRODUCT(--(MOD((1:1),2)=1),--(1:1))
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
Akhilesh Dalia <Akhilesh wrote in message
...
How do I get sum of all odd or even numbers from the series (1, 2, 3,
......,
n) by using SUMIF function and MOD function or any other function?

Note that all the suggested formulas are reading the entire row1
hence these formulas have to be entered in cells *other*
than those in row1, otherwise you'll get circular ref errors !

If you need to enter the formulas in the same row,
use range references (e.g.: A1:F1), i.e. something like:

In G1: =SUMPRODUCT(--(MOD((A1:F1),2)=0),A1:F1)
Then G1 can be copied down to evaluate similarly for other
series in A2:F2, A3:F3, etc

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Thank you very much!!

I got my job done. You provided a great help.

Thanks once again!!

Regards

Akhilesh Dalia

"Max" wrote:

Note that all the suggested formulas are reading the entire row1
hence these formulas have to be entered in cells *other*
than those in row1, otherwise you'll get circular ref errors !

If you need to enter the formulas in the same row,
use range references (e.g.: A1:F1), i.e. something like:

In G1: =SUMPRODUCT(--(MOD((A1:F1),2)=0),A1:F1)
Then G1 can be copied down to evaluate similarly for other
series in A2:F2, A3:F3, etc

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Glad to hear it helped !
Thanks for the feedback ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
Akhilesh Dalia <Akhilesh wrote in message
...
Thank you very much!!

I got my job done. You provided a great help.

Thanks once again!!

Regards

Akhilesh Dalia

"Max" wrote...
One way:

Assuming series / numbers is in row1

Sum of Even:
=SUMPRODUCT(--(MOD((1:1),2)=0),1:1)

Sum of Odd:
=SUMPRODUCT(--(MOD((1:1),2)=1),1:1)
....

The original question looked like it could have been solved using standard
variations on Gauss's formula.

Sum of odd numbers from 1 to N = ((N + 1)/2)^2 for N odd.

Sum of even numbers from 2 to N = N * (N + 2)/4 for N even.