Help in Excel 2000

[Akhilesh Dalia]

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?

 

[Max]

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

 

[Max]

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

 

[Akhilesh Dalia]

Thank you very much!!

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

Thanks once again!!

Regards

Akhilesh Dalia

 

[Max]

Glad to hear it helped !
Thanks for the feedback ..

 

[Harlan Grove]

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.