sum odd numbers

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

can anyone tell me a formula that will sum just the even numbers or the odd
numbers in a range?
 
Even:

=SUM(IF(MOD($A$1:$A$10,2)=0,$A$1:$A$10))

Odd:

=SUM(IF(MOD($A$1:$A$10,2)=1,$A$1:$A$10))

ENTER both with Ctrl+Shift+Enter

You will get {} round the formula if entered correctly.
 
=SUMPRODUCT(MOD(A1:A6,2),A1:A6)

That is for odd numbers... I guess you can use this for even numbers...

=SUMPRODUCT(1-MOD(A1:A6,2),A1:A6)

Rick
 
Test it, more intuitive

=SUMPRODUCT(--(MOD(A1:A6,2)=0),A1:A6)

Pity about ISEVEN

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Thank you all for your help. I dont really understand the MOD() formula but
all of your suggestions worked, with array and without array.
 
=SUMPRODUCT(MOD(A1:A6,2),A1:A6)
Test it, more intuitive

=SUMPRODUCT(--(MOD(A1:A6,2)=0),A1:A6)
Click to expand...

More intuitive? Well, marginally (at least for me). Since I had no trouble
seeing that Bernd's MOD(A1:A6,2) yields 1 when the processed cell is odd and
0 when it is even, I find no difficulty seeing that subtracting these values
(0 or 1) from 1 reverses the values (they become 1 or 0 respectively) and,
hence, their odd/even-ness (it is nothing more than the principal of
toggling a value between 0 and 1 inside a program where the code line would
be Value=1-Value).
Pity about ISEVEN
Click to expand...

I am newly returned to Excel after a very long absence and am puzzled by
this. Why is it that some functions (for example, MOD) can use array ranges
in this way and others (like ISEVEN) can't? Is there some "rule" governing
which function can and cannot?

Rick
 
Thank you all for your help. I dont really understand the MOD() formula

The MOD function returns the remainder after division. So, for this...

=MOD(17,5)

look at the division 17/5, the answer is 3 with a remainder of 2. The above
formula returns the remainder, in this case, 2. Now, for odd numbers, when
divided by 2, they always have a remainder of 1. So, the expression
MOD(SomeNumber,2) will evaluate to 1 whenever SomeNumber is odd. Also, an
even number always has a remainder of 0 (2 always divides even numbers
evenly, sort of by definition). So MOD(SomeNumber,2) will evaluate to 0
whenever SomeNumber is even. The SUMPRODUCT formula that Bernd posted makes
use of this to multiply the odd numbers by their return value of 1 and the
even numbers by their return value of 0, thus resulting in the addition of
only the odd numbers.

Rick
 
For functions that you don't understand, I recommend that you use Excel's
help facility. It will explain, usually give examples, & often also
cross-refer to related functions.
 
Rick Rothstein (MVP - VB) said:
More intuitive? Well, marginally (at least for me). Since I had no trouble
seeing that Bernd's MOD(A1:A6,2) yields 1 when the processed cell is odd
and 0 when it is even, I find no difficulty seeing that subtracting these
values (0 or 1) from 1 reverses the values (they become 1 or 0
respectively) and, hence, their odd/even-ness (it is nothing more than the
principal of toggling a value between 0 and 1 inside a program where the
code line would be Value=1-Value).
Click to expand...

But what if it were numbers divisible by 3? Try constructing the formula
with your functions, it ain't easy.

But with mine, it is simply

=SUMPRODUCT(--(MOD(A1:A6,3)=0),A1:A6)

Intuitive to get from one to another.

I am newly returned to Excel after a very long absence and am puzzled by
this. Why is it that some functions (for example, MOD) can use array
ranges in this way and others (like ISEVEN) can't? Is there some "rule"
governing which function can and cannot?
Click to expand...


I don't know for sure, but I guess it is just because that Excel is such a
big program that different parts were developed by different teams. One team
developed all of their functions to return arrays, one didn't. The one that
annoys me most is WEEKDAY/WEEKNUM. WEEKDAY does, WEEKNUM doesn't.
 
=SUMPRODUCT(MOD(A1:A6,2),A1:A6)
But what if it were numbers divisible by 3? Try constructing the formula
with your functions, it ain't easy.

But with mine, it is simply

=SUMPRODUCT(--(MOD(A1:A6,3)=0),A1:A6)

Intuitive to get from one to another.
Click to expand...

Ahhhh... that "intuitive". <g>

Rick
 
The one that annoys me most is...WEEKNUM

For me, it's RANK (athough there are work-arounds).

Biff
 
There are with them all Biff, but it would be nice if ...

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Back
Top