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

"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

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.

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

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

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

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

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.

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

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.