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

Hi there!

To count the number of even or odd numbers in a column, you can use the COUNTIF function with a criteria that checks if the number is even or odd.

Here's how you can do it:

1. Insert a new column next to the column with the numbers you want to count.
2. In the first cell of the new column, enter the formula =MOD(A1,2), where A1 is the first cell of the column with the numbers you want to count. This formula will return 0 if the number is even, and 1 if the number is odd.
3. Copy the formula down to the rest of the cells in the new column.
4. To count the number of even numbers, use the formula =COUNTIF(B:B,0), where B:B is the column with the results of the MOD formula. This formula will count the number of cells in the column that contain 0, which represents even numbers.
5. To count the number of odd numbers, use the formula =COUNTIF(B:B,1), where B:B is the column with the results of the MOD formula. This formula will count the number of cells in the column that contain 1, which represents odd numbers.

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

"Jezzy" wrote:

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

Try these:

Even numbers:

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

Odd numbers:

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

--
Biff
Microsoft Excel MVP


"Jezzy" wrote in message
...
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

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


"Jezzy" wrote in message
...
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

"T. Valko" wrote...
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

Hi All,

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


Jez

"MartinW" wrote:

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


"Jezzy" wrote in message
...
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

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


"Jezzy" wrote in message
...
Hi All,

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


Jez

"MartinW" wrote:

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


"Jezzy" wrote in message
...
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

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.


--
Thanks,
Shane Devenshire


"Jezzy" wrote:

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

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

"MartinW" wrote:

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


"Jezzy" wrote in message
...
Hi All,

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


Jez

"MartinW" wrote:

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


"Jezzy" wrote in message
...
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

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

--
Biff
Microsoft Excel MVP


"Jezzy" wrote in message
...
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

"MartinW" wrote:

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


"Jezzy" wrote in message
...
Hi All,

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


Jez

"MartinW" wrote:

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


"Jezzy" wrote in message
...
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

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" wrote:

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

--
Biff
Microsoft Excel MVP


"Jezzy" wrote in message
...
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

"MartinW" wrote:

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


"Jezzy" wrote in message
...
Hi All,

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


Jez

"MartinW" wrote:

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


"Jezzy" wrote in message
...
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

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


--
Biff
Microsoft Excel MVP


"Jezzy" wrote in message
...
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" wrote:

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

--
Biff
Microsoft Excel MVP


"Jezzy" wrote in message
...
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

"MartinW" wrote:

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


"Jezzy" wrote in message
...
Hi All,

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


Jez

"MartinW" wrote:

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


"Jezzy" wrote in message
...
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

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

--
Biff
Microsoft Excel MVP


"ShaneDevenshire" wrote in
message ...
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.


--
Thanks,
Shane Devenshire


"Jezzy" wrote:

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

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.
--
** John C **


"Harlan Grove" wrote:

"T. Valko" wrote...
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

John C <johnc@stateofdenial wrote...
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.

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.
--
** John C **


"Harlan Grove" wrote:

John C <johnc@stateofdenial wrote...
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 <johnc@stateofdenial wrote...
....
should reconsider that instead of treating everyone like an idiot.

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

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.
--
** John C **

"Harlan Grove" wrote:

John C <johnc@stateofdenial wrote...
....
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 <johnc@stateofdenial wrote...
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?

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.

--
** John C **
Please remember, if your question is answered, to check the YES box below.
It helps everyone.


"Harlan Grove" wrote:

John C <johnc@stateofdenial wrote...
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?

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.

What do I need to defend myself from?

I don't think Harlan was being condescending towards me in his reply. He
simply noted some possible valid issues and I do the same at times.

However, I stand by my suggested formulas based on the information provided
by the OP. It's also true, as Harlan noted, that posters often oversimplify
their requests and this can lead to numerous follow-ups but I don't mind
follow-ups.

I'm all for *efficient robustness* but at some point unnecessary robustness
crosses the line into overkill. If we wanted to take this example to the
extreme none of us accounted for the possibility of there being text entries
in the range. Reading the OP one would not be led to believe that there were
text entries so accounting for that when not needed woud be overkill.

--
Biff
Microsoft Excel MVP


"Harlan Grove" wrote in message
...
John C <johnc@stateofdenial wrote...
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?

Your right Biff, and I apologize to you. My ire gets up when I see something
like his post. The point I took offense to was this:
=SUMPRODUCT(--(MOD(A1:A10,2)0))
....
This isn't.
Your formula worked perfectly fine according to the OP specs, and with the
sample data showing only whole numbers (no blanks, decimals, or text), your
formula is OK.
I think I would have remained completely silent if instead he had said
something like:
This could be improved by. But to say flat out that your formula was not
going to do it, well, just got to me, far more than it should have.
I respect both your knowledge, and Harlan's knowledge, and as I stated
several times, you 2 have been on here for a long time, and have helped
countless people along the line. For that, I commend you both. There are
times, in my opinion, though that a responder oversteps the bounds of
courtesy, and telling people they are stupid, (or in this case, telling you
that you are wrong, when in fact you aren't), and I get tired of it.

--
** John C **
Please remember, if your question is answered, to check the YES box below.
It helps everyone.


"T. Valko" wrote:

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.

What do I need to defend myself from?

I don't think Harlan was being condescending towards me in his reply. He
simply noted some possible valid issues and I do the same at times.

However, I stand by my suggested formulas based on the information provided
by the OP. It's also true, as Harlan noted, that posters often oversimplify
their requests and this can lead to numerous follow-ups but I don't mind
follow-ups.

I'm all for *efficient robustness* but at some point unnecessary robustness
crosses the line into overkill. If we wanted to take this example to the
extreme none of us accounted for the possibility of there being text entries
in the range. Reading the OP one would not be led to believe that there were
text entries so accounting for that when not needed woud be overkill.

--
Biff
Microsoft Excel MVP


"Harlan Grove" wrote in message
...
John C <johnc@stateofdenial wrote...
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 <johnc@stateofdenial wrote...
If a column is set up with error trapping so that it only contains integers,
....

You haven't written many workbooks for other people to use.

Set up whatever data validation rules you want to ENSURE only integers
are valid entries, type (but don't press enter) 0.125, press [F2],
[Shift]+[Home], [Ctrl]+C, [Esc], [Ctrl]+V.

Try to use event handlers - open the workbook with macros disabled.

...You haven't written many workbooks for other people to use....

I have. It is why I automate processes that would otherwise require
copy/paste, or use macros to define the copy/paste. It is why I create macros
with my digital signature, and then I ensure my digital signature is in the
user's trusted sources.
--
** John C **

Hi All,

I am sorry if I cause an uproar here. It's my first time in this forum. I
also have very basic knowledge of Excel. I am self-employed and trying to
get myself familiar with the program. Maybe in future if I come across any
difficulties I will provide more details and proper questions. My apology.
I do appreciate the generous help given by all you people.

"Jezzy" wrote:

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

You did not cause an uproar and there is no need to apologize!

This forum is here to provide peer to peer help. It seems that you got more
help than you probably expected but the bottom line is that your question
was answered and it was answered correctly. The thing to understand about
Excel is that there are almost always *many* different ways to do something
and if you get exposed to more than one of those ways then you're able to
evaluate each and choose which is best for your application.

Please don't hesitate to ask questions in the future.

--
Biff
Microsoft Excel MVP


"Jezzy" wrote in message
...
Hi All,

I am sorry if I cause an uproar here. It's my first time in this forum.
I
also have very basic knowledge of Excel. I am self-employed and trying to
get myself familiar with the program. Maybe in future if I come across
any
difficulties I will provide more details and proper questions. My
apology.
I do appreciate the generous help given by all you people.

"Jezzy" wrote:

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

John C <johnc@stateofdenial wrote...
....
. . . It is why I automate processes that would otherwise require
copy/paste, or use macros to define the copy/paste. It is why I create macros
with my digital signature, and then I ensure my digital signature is in the
user's trusted sources.

And how many OPs do you suppose do this?

Maybe you could spend hours implementing data transfer and validation
procedures that would render it possible to use =SUMPRODUCT(--(MOD(x,
2)0)) to return the correct count of odd numbers. But unless there
were other reasons for it, I'd save the time and effort and use
=SUMPRODUCT(--(MOD(x,2)=1)) and get the same result.

Jezzy wrote...
I am sorry if I cause an uproar here. . . .

You didn't. Please don't believe that you caused the fuss. It was a
tempest among the respondents only. That said, more details never hurt.

You go your way, I'll go my way. Biff's answer was a good solution, if you
don't like it, that's on you.

In regards to your comment, I notice you keep flip-flopping back and forth.
One moment, you criticize the formula, then you criticize the question, then
you criticize me(...You haven't written many workbooks for other people to
use...). Make up your mind which it is going to be.

And, as has been pointed out extensively throughout the community, no matter
what protections, error trapping, passwords, redundant calculations, etc. are
done in a workbook, any user can break anything. So I ask you, why bother
with error trapping at all?
--
** John C **

I concur with both Harlan and Biff. Do not feel blamed, or feel sorry for it.
Use the forum extensively. Both Harlan and Biff are very knowledgeable
responders, and there are many other responders who have a vast wealth of
knowledge who are here to help others. Never hesitate to use the community.
--
** John C **

John C <johnc@stateofdenial wrote...
....
And, as has been pointed out extensively throughout the community, no matter
what protections, error trapping, passwords, redundant calculations, etc. are
done in a workbook, any user can break anything. So I ask you, why bother
with error trapping at all?

You're the one who suggested validation checking, which is an implicit
requirement when using the formula

=SUMPRODUCT(--(MOD(x,2)0))

I didn't suggest anything other than using a formula that ALWAYS
returns the count of odd numbers no matter what the cell contents may
be (unless the cells evaluate to error values). The formula I
proposed,

=SUMPRODUCT(--(MOD(x,2)=1))

doesn't need error trapping. It always works unless there are error
values or numbers with integer parts greater than 2^28-1. Handling
that last case requires the other formula I gave,

=SUMPRODUCT(--(x-2*INT(x/2)=1))

This truly requires no error trapping and/or data validation, though
it propagates error values in x. IOW, it'll either work (return the
count of odd numbers in x) or return an error value. It'll NEVER
return a wrong numeric result no matter how users torture or misuse
the workbook. Since you claim to know something about spreadsheet
development, this level of robustness isn't useful? You do understand
the meanings of the words 'robust' and 'robustness', don't you?

You may believe I've been flip-flopping, but only because I've been
responding to all the nonsense and blather you've been spewing. In
order, Biff's formula could be improved upon, you don't understand the
concept of robustness, and you're willing to wander off into pastures
like this 'If a column is set up with error trapping so that it only
contains integers'. Assume there's no data problems, and the universe
of workable formulas may increase, but it begs the question whether
it's better to assume perfect data or dirty data. I'll assume the
latter.

And evidently the fact that I thought Biff's formula could be improved
upon was/is 'on you'. That or your continued responses would serve as
proof of your irrationality. So which is it: you're continuing this
argument because, contrary to your assertions, this does matter to you
(but you'll continue to refuse either to comprehend the robustness
issue or acknowledge comprehending it) or because you're irrational?

I am quite literate, and know the words of robust, and robustness.
Ooops, I deleted your formula, now it gives no answer.
But, according to you
....It'll NEVER return a wrong numeric result no matter how users torture or
misuse
the workbook...

And of course, the best one you said:
....It always works unless ...

Well, another flip flop

....This isn't (OK up to MOD's standars)... referring to Biff's original
formula
....And evidently the fact that I thought Biff's formula could be improved
upon...

You first state that the formula isn't OK. Which, in fact, it is OK. I agree
that it isn't as robust as other solutions, but it IS OK. Now, you say that
the formula could be improved upon. My whole reason of responding, the entire
thread, was kicked off by this tidbit. If you had stated, correctly, that the
formulas could be improved upon, hey, more power to you, perfectly
understandable. But to say the formula didn't work / meet the OPs required
needs was obviously wrong on 'your' part. No, not irrational, and I notice
you keep posting to this one as well, so obviously it matters somewhat to you.

How about leaving me out of this?

--
Biff
Microsoft Excel MVP


"Harlan Grove" wrote in message
...
John C <johnc@stateofdenial wrote...
...
And, as has been pointed out extensively throughout the community, no
matter
what protections, error trapping, passwords, redundant calculations, etc.
are
done in a workbook, any user can break anything. So I ask you, why bother
with error trapping at all?

You're the one who suggested validation checking, which is an implicit
requirement when using the formula

=SUMPRODUCT(--(MOD(x,2)0))

I didn't suggest anything other than using a formula that ALWAYS
returns the count of odd numbers no matter what the cell contents may
be (unless the cells evaluate to error values). The formula I
proposed,

=SUMPRODUCT(--(MOD(x,2)=1))

doesn't need error trapping. It always works unless there are error
values or numbers with integer parts greater than 2^28-1. Handling
that last case requires the other formula I gave,

=SUMPRODUCT(--(x-2*INT(x/2)=1))

This truly requires no error trapping and/or data validation, though
it propagates error values in x. IOW, it'll either work (return the
count of odd numbers in x) or return an error value. It'll NEVER
return a wrong numeric result no matter how users torture or misuse
the workbook. Since you claim to know something about spreadsheet
development, this level of robustness isn't useful? You do understand
the meanings of the words 'robust' and 'robustness', don't you?

You may believe I've been flip-flopping, but only because I've been
responding to all the nonsense and blather you've been spewing. In
order, Biff's formula could be improved upon, you don't understand the
concept of robustness, and you're willing to wander off into pastures
like this 'If a column is set up with error trapping so that it only
contains integers'. Assume there's no data problems, and the universe
of workable formulas may increase, but it begs the question whether
it's better to assume perfect data or dirty data. I'll assume the
latter.

And evidently the fact that I thought Biff's formula could be improved
upon was/is 'on you'. That or your continued responses would serve as
proof of your irrationality. So which is it: you're continuing this
argument because, contrary to your assertions, this does matter to you
(but you'll continue to refuse either to comprehend the robustness
issue or acknowledge comprehending it) or because you're irrational?

How about leaving me out of this?

--
Biff
Microsoft Excel MVP


"John C" <johnc@stateofdenial wrote in message
...
I am quite literate, and know the words of robust, and robustness.
Ooops, I deleted your formula, now it gives no answer.
But, according to you
...It'll NEVER return a wrong numeric result no matter how users torture
or
misuse
the workbook...

And of course, the best one you said:
...It always works unless ...

Well, another flip flop

...This isn't (OK up to MOD's standars)... referring to Biff's original
formula
...And evidently the fact that I thought Biff's formula could be improved
upon...

You first state that the formula isn't OK. Which, in fact, it is OK. I
agree
that it isn't as robust as other solutions, but it IS OK. Now, you say
that
the formula could be improved upon. My whole reason of responding, the
entire
thread, was kicked off by this tidbit. If you had stated, correctly, that
the
formulas could be improved upon, hey, more power to you, perfectly
understandable. But to say the formula didn't work / meet the OPs required
needs was obviously wrong on 'your' part. No, not irrational, and I notice
you keep posting to this one as well, so obviously it matters somewhat to
you.

John C <johnc@stateofdenial wrote...
I am quite literate, and know the words of robust, and robustness.
Ooops, I deleted your formula, now it gives no answer.
But, according to you
...It'll NEVER return a wrong numeric result no matter how users torture or misuse
the workbook...

Yup, so it doesn't give the WRONG answer. Apparently the concept that
NO answer is equal to NEITHER a right answer nor a wrong answer has
yet to enter your oh, so literate mind. Literate perhaps, but still
ignorant.

To repeat in the vain hope of penetrating your simple mind, no answer
isn't a wrong answer. It's no answer.

And of course, the best one you said:
...It always works unless ...

Well, another flip flop

Ah, logic! Rather the lack of it.

You're now explicitly resorting to 3-year-old's arguments. I could say
the sun will rise tomorrow, and you will say either that the sun could
explode or the earth stop rotating before that. Do I really need to
preface my statement that IF ANY OF US SURVIVE TO TEST THIS, the sun
will rise tomorrow? Knowing you, you'd then posit the earth
transported into some region between galaxies with billions of alien
spaceships pointing heat lamps at earth in order to construct a
scenario in which some of us do survive but the sun doesn't rise.

I wonder what you'll come up with next.

...This isn't (OK up to MOD's standars)...

OK, if you require it, up to MOD's standards *AND* assuming users
don't deliberately or accidentally clear or delete cells containing
formulas, or change their system fonts, or delete necessary DLLs, or
corrupt their registries, or remove necessary subsystems like video
cards/chips, or damage hardware, or gouge out their eyes or chop off
their fingers.

You first state that the formula isn't OK. Which, in fact, it is OK. I agree
that it isn't as robust as other solutions, but it IS OK. . . .

It's OK if the range contains no numbers with fractional parts.
Probably OK if the data in the range comes from an outside system that
only records integers. Less likely OK if the range contains formulas.

Now, you say that the formula could be improved upon. . . .

As in made more general - yes. It could be improved upon by being made
more general. OTOH, someone else who will remain nameless improved
upon it under the assumptions the range would only contain integers,
namely,

=SUMPRODUCT(MOD(x,2))

. . . But to say the formula didn't work / meet the OPs required
needs was obviously wrong on 'your' part. . . .

The original formula works when all entries are integers. That
satisfies the OP's specs. However, as I mentioned before, newsgroup
archives serve as FAQs, and the original formula doesn't work in
general. Neither did the other formula, count of even numbers, if
there could be text or blank cells in the range.

So if the range in question consisted of integers only < 2^28, then
both original formulas in this thread give the correct results. But if
there were anything else in the range, both formulas could return
incorrect numeric results. Generalizing the count of even numbers was
dealt with in a different branch, so I didn't mention it.

you keep posting to this one as well, so obviously it matters somewhat to you.

Unlike you, I wasn't foolish enough to state, 'and that's pretty much
all I'm gonna waste on this'. You lack self-discipline too? Or just
enjoy affecting insincere faux-principled positions?