Countif function for Even and Odd numbers

[Jezzy]

Hi Hi,

I need help with a formula. I would like to count a column that consists of
even and odd numbers eg. 28, 31, 43, 50, 60. I like to count how many
numbers in that column is odd or even.

I have tried the following but it didn't work

=countif(a1:a10,"1","3","5")

Appreciate some help here.

Thanks

 

[Mike H]

Try this

=COUNT(IF(MOD(A1:A25,2),A1:A25))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
and NOT just enter. If you do it correctly then Excel will put curly brackets
around it {} you can't type these yourself.

Mike

 

[T. Valko]

Try these:

Even numbers:

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

Odd numbers:

=SUMPRODUCT(--(MOD(A1:A10,2)>0))

 

[MartinW]

Hi,

Saved from a previous post,

This will count the EVENS
=SUMPRODUCT(--(A1:A10<>""),--(MOD(A1:A10,2)=0))

And this the ODDS
=SUMPRODUCT(--(A1:A10<>""),--(MOD(A1:A10,2)=1))

HTH
Martin

 

[Harlan Grove]

Try these:

Even numbers:

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

This one's OK up to MOD's capabilities.

Odd numbers:

=SUMPRODUCT(--(MOD(A1:A10,2)>0))

....

This isn't. Technically, numbers with fractional parts aren't either
even or odd. Even if 1.5 should be considered odd and 2.125 even, your
formula would treat both as odd. The correct formula for odd integers
is

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

Then there's the problem that Excel's MOD function isn't reliable.
Among all applications currently supported which handle double
precision math, Excel's MOD function has a uniquely artificially
curtailed domain. Safer to use

=SUMPRODUCT(--(A1:A10-2*INT(A1:A10/2)=0)) for even integers

=SUMPRODUCT(--(A1:A10-2*INT(A1:A10/2)=1)) for odd integers

 

[Jezzy]

Hi All,

Thanks for the reply. I tried MartinW's method and it works. Appreciate
all your help very much.


Jez

 

[MartinW]

Hi Jez,

That's not my method, it's one I saved from a post a couple
of years ago. It may have been Biff's although I think more
likely it was Bob Phillips. You may also want to take note
of Harlan Grove's comments on the use of MOD and INT.

Anyway, all up it sounds like you found what you were
looking for so that's the main thing.

Cheers
Martin

 

[ShaneDevenshire]

Hi,

Since your sample numbers are integers, and noting Harlan's correct
observations

You might simplify your calculations one small way, enter the following in A10
=SUMPRODUCT(--MOD(A1:A9,2)) for ODD's
And in A11
=COUNT(A1:A9)-A10 for EVEN's

Or substitue one of Harlan's formulas for the first one.

 

[Jezzy]

Hi MartinW and Harlan,

I tried the method by Harlan Grove. When filling in the formula for "odd"
it's fine. When I input the formula for "even" it seems a little weird
because after I cleared all the numbers in the column, under the "odd"
formula cell it's gives me a "zero" which is correct but under the "even"
formula cell it came out the number "9" when there was nothing to count. My
table consists of 9 rows of numbers in every column.

Maybe he can explain where the error lies.

Thanks.

Jez

 

[T. Valko]

It's counting empty cells.

Here are my thoughts on this. Based on the limited sample you posted it
looks like you're dealing with integers. While Harlan is correct on the
points he made I think it's a bit of overkill if:

....the numbers you're dealing with are *always* integers
....the numbers are *always* less than ~200,000,000

Try this version of Harlan's formula:

=SUMPRODUCT(--(ISNUMBER(A1:A10)),--(A1:A10-2*INT(A1:A10/2)=0))

 

[Jezzy]

Hi T. Valko,

Thanks! The version you showed below works. So, if I were to use your
version for the "odd" formula should be

=sumproduct (--(ISNUMBER(A1:A10)),--(A1:A10-2*INT(A:A10/2)=1))

I didn't realize that there are so many ways of setting arguments in a
formula. Although the method shown by MartinW is much easier (less input), I
guess it doesn't hurt to learn more ways.

Thanks alot guys!

Jez

 

[T. Valko]

version for the "odd" formula should be

=sumproduct (--(ISNUMBER(A1:A10)),--(A1:A10-2*INT(A:A10/2)=1))

You don't need the ISNUMBER test. An empty cell evaluates to 0. On an empty
cell this portion will *always* evaluate to 0:

cell_ref-2*INT(cell_ref/2)

So:

0=1 will be FALSE and therefore not counted.

=SUMPRODUCT(--(A1:A10-2*INT(A1:A10/2)=1))

 

[T. Valko]

You might simplify your calculations one small way,

=SUMPRODUCT(--MOD(A1:A9,2)) for ODD's

You can also remove the double unary.

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

 

[John C]

I guess the OP declaration of ...I would like to count a column that
consists of
even and odd numbers ... means nothing then.

Your argument, while technically correct, is unnecessary, as, per the OP,
the column will contain either even numbers or odd numbers.

 

[Harlan Grove]

I guess the OP declaration of  ...I would like to count a column that
consists of even and odd numbers ... means nothing then.

Not nothing, but little. Follow the newsgroups for a while and you'll
find that most OPs oversimplify their examples. If and when you gain
some experience responding in newsgroups, you may learn to anticipate
this.

Also, Google archives newsgroups, and some people (not many given the
frequency that the same questions get asked and answered) review the
archives for answers to commonly asked questions. While this OP may be
working only with integers, others who read this thread later could be
working with nonintegers.

Your argument, while technically correct, is unnecessary, as, per the OP,
the column will contain either even numbers or odd numbers.

Robustness is good. The formula

=SUMPRODUCT(--(MOD(A1:A10,2)>0))

is OBJECTIVELY LESS ROBUST than

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

If robustness means little or nothing to you, pity for your employer
and co-workers.

 

[John C]

I only responded to you because all you seem to do is belittle others in your
posts. Pointing out an error is one thing. But if an OP says they have a
column of odd and even numbers, and then gives an example of numbers, all of
which are whole numbers, and therefore, odd or even, then perhaps you can
expect that the OP actually knows what they are talking about. Perhaps you
should reconsider that instead of treating everyone like an idiot.

 

[Harlan Grove]

should reconsider that instead of treating everyone like an idiot.

Read more of my posts. I only treat people like you like idiots.

 

[John C]

I have read many of your posts, and you are constantly condescending. People
come here for help when they are confused and/or under the gun. Treating them
like an idiot is not helpful. You have done it so many times I cannot count
that high. I do not question your excel knowledge, you know quite a lot, and
you have given a lot of good advice for people who have been confused and
have been under the gun. But you do have to be a condescending elitist snob
when you do it. That's all I am saying.

If the OP had said, they had a list of numbers, and then needed to know how
many odd numbers and how many even numbers, then sure, I'd even provide error
trapping in a response. But, when an OP states they have a list of odd/even
numbers, and then proceeds to give the example of WHOLE numbers which will
either be odd or even by definition, then no, I wouldn't provide error
trapping. If someone else in their normal response provided error trapping,
that is just fine. But to call someone out, and especially when it is someone
else who's excel knowledge so many people here have tapped, and say that
because they did not provide the error trapping that their formula wasn't
"robust" enough, that's just plain, well, condescending.

Like I said, your excel knowledge is vast, and you have helped many a
person, but get down off your high horse and treat other with respect. Just
my 2 cents, and that's pretty much all I'm gonna waste on this, and you, any
more.

 

[Harlan Grove]

I have read many of your posts, and you are constantly condescending. . . .

Biff (T. Valko) can defend himself. Besides, I didn't think I was
being condescending to him. I may have been pedantic and
overengineering, but not condescending. I wasn't responding to the OP.

As for you, you want to correct me. From your perspective, I'm
incorrigible. Are you too stupid to realize this?

. . . but get down off your high horse and treat other with respect. . . .

I do treat people with respect by default. Also definitely when they
deserve it. However, people like you who regret having grown too old
to be hall monitors and now look for other ways to make other people
do what you want them to do get what you get, at least not in the
threads where you're acting foolish.

Finally, without the condescension, to repeat:

Robustness is good. The formula

=SUMPRODUCT(--(MOD(A1:A10,2)>0))

is OBJECTIVELY LESS ROBUST than

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


Robustness isn't worthwhile?

The last formula will ALWAYS return the count of odd integers in
A1:A10 even when that range contains nonintegers and nonnumbers. Only
when there are error values in A1:A10 (or values greater than 2^28-1)
would it return something else (error values). Wouldn't that make it
more generally applicable and less subject to bugs in subsequent use?

 

[John C]

If a column is set up with error trapping so that it only contains integers,
why on earth would you want to check to see if it has integers again?
And remember, every function, or error check that is added takes up more
memory, and more calculating processing time by the computer. So if someone
has a column that will only contain whole numbers, why get redundant and chew
on more resources?
As far as calling names, I figured you would come to that, it is very like
you from all the posts of yours I read. Truly sad. Truly.
And my correction of you is basically the same you said to Biff. So
obviously I am not being condescending or accusatory, eh?
Not responding to the OP, then why respond at all? What was it that stuck in
your craw so much that Biff had made such an egregious error that you felt
the need to post? Now, if you were correcting an error made by a responder so
that the OP would know that an error exists, then I totally understand. But,
like you just said, you weren't responding to the OP, so why post? I know
why, because you are on your high horse, and you glean that someone may have
left some (as I pointed out, quite unnecessary) error trapping out. And while
we are at it, what happens, just curious, if one of those pesky alphabet
characters get in the way? hmmmm? You are so keen on error trapping, and
since you have expanded the fact that the cells can now contain decimal
numbers on top of integers (as the OP portrayed), well, what happens with
letters? Or, better, yet, what if is something like:
A2: =IF(B2=1,1,"")
Oh my goodness, then your formula breaks with old pesky #VALUE error. Now
what are you going to do? I mean heck, Biff missed that one too, didn't he?
*sigh*, you remind me of someone back in elementary school, but I digress.