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