sum odd numbers
 

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

sum odd numbers
 

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.

"twty" wrote:

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

sum odd numbers
 

Hello,

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

No array formula.

Regards,
Bernd

sum odd numbers
 

=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

sum odd numbers
 

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)

"Rick Rothstein (MVP - VB)" wrote in
message ...
=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

sum odd numbers
 

Thank you all for your help. I dont really understand the MOD() formula but
all of your suggestions worked, with array and without array.

"Bob Phillips" wrote:

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)

"Rick Rothstein (MVP - VB)" wrote in
message ...
=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

sum odd numbers
 

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

Test it, more intuitive

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

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

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

sum odd numbers
 

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

sum odd numbers
 

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.
--
David Biddulph

"twty" wrote in message
...
Thank you all for your help. I dont really understand the MOD() formula
but
all of your suggestions worked, with array and without array.

sum odd numbers
 

"Rick Rothstein (MVP - VB)" wrote in
message ...
=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)

Test it, more intuitive

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

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

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.



Pity about ISEVEN

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?


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.

sum odd numbers
 

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

Test it, more intuitive

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

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

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.

Ahhhh... that "intuitive". <g

Rick

sum odd numbers
 

The one that annoys me most is...WEEKNUM

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

Biff

"Bob Phillips" wrote in message
...

"Rick Rothstein (MVP - VB)" wrote in
message ...
=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)

Test it, more intuitive

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

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

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.



Pity about ISEVEN

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?


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.

sum odd numbers
 

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)

"T. Valko" wrote in message
...
The one that annoys me most is...WEEKNUM

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

Biff

"Bob Phillips" wrote in message
...

"Rick Rothstein (MVP - VB)" wrote in
message ...
=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)

Test it, more intuitive

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

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

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.



Pity about ISEVEN

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?


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.