Counting # of cells
 

I need a formula to count the number of cells in a column that contain the
numbers between 120 and 129. Thanks for any input!
--
Ronnie

Counting # of cells
 

=COUNTIF(A1:A100, "=120") - COUNTIF(A1:A100, "<=129")
or
=SUMPRODUCT(--(A1:A100=120),--(A1:A100<=129))
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Ronnie" wrote in message
...
I need a formula to count the number of cells in a column that contain the
numbers between 120 and 129. Thanks for any input!
--
Ronnie

Counting # of cells
 

the column being 'A', it could be written this way:

=COUNTIF(A:A,"<130")-COUNTIF(A:A,"<120")

"Ronnie" wrote:

I need a formula to count the number of cells in a column that contain the
numbers between 120 and 129. Thanks for any input!
--
Ronnie

Counting # of cells
 

I think you mistyped on your COUNTIF
I believe it should be:
=COUNTIF(A1:A100, "=120") - COUNTIF(A1:A100, "129")
--
** John C **


"Bernard Liengme" wrote:

=COUNTIF(A1:A100, "=120") - COUNTIF(A1:A100, "<=129")
or
=SUMPRODUCT(--(A1:A100=120),--(A1:A100<=129))
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Ronnie" wrote in message
...
I need a formula to count the number of cells in a column that contain the
numbers between 120 and 129. Thanks for any input!
--
Ronnie

Counting # of cells
 

I am so close, but it's not working. I think it's the minus sign in the
formula. Any other suggestions.
--
Ronnie


"Ryan Martin" wrote:

the column being 'A', it could be written this way:

=COUNTIF(A:A,"<130")-COUNTIF(A:A,"<120")

"Ronnie" wrote:

I need a formula to count the number of cells in a column that contain the
numbers between 120 and 129. Thanks for any input!
--
Ronnie

Counting # of cells
 

The sumproduct formulat worked. THANKS!!
--
Ronnie


"Bernard Liengme" wrote:

=COUNTIF(A1:A100, "=120") - COUNTIF(A1:A100, "<=129")
or
=SUMPRODUCT(--(A1:A100=120),--(A1:A100<=129))
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Ronnie" wrote in message
...
I need a formula to count the number of cells in a column that contain the
numbers between 120 and 129. Thanks for any input!
--
Ronnie

Counting # of cells
 

What makes you think that the minus sign is wrong? The formula gives the
right result if the values are integer and the "between" is intended to be
inclusive.
It would be better to use =COUNTIF(A:A,"<=129")-COUNTIF(A:A,"<120") in case
the numbers are not necessarily integer.
Change it to =COUNTIF(A:A,"<129")-COUNTIF(A:A,"<=120") if "between" is
intended to be exclusive, not inclusive.
--
David Biddulph

"Ronnie" wrote in message
...
I am so close, but it's not working. I think it's the minus sign in the
formula. Any other suggestions.
--
Ronnie


"Ryan Martin" wrote:

the column being 'A', it could be written this way:

=COUNTIF(A:A,"<130")-COUNTIF(A:A,"<120")

"Ronnie" wrote:

I need a formula to count the number of cells in a column that contain
the
numbers between 120 and 129. Thanks for any input!
--
Ronnie

Counting # of cells
 

I'm not sure what was wrong. I was unable to get the counts to come out
properly. I did get the "sumproduct" formula to work. I would like to know
why the other formula was not working, but it wasn't.
--
Ronnie


"David Biddulph" wrote:

What makes you think that the minus sign is wrong? The formula gives the
right result if the values are integer and the "between" is intended to be
inclusive.
It would be better to use =COUNTIF(A:A,"<=129")-COUNTIF(A:A,"<120") in case
the numbers are not necessarily integer.
Change it to =COUNTIF(A:A,"<129")-COUNTIF(A:A,"<=120") if "between" is
intended to be exclusive, not inclusive.
--
David Biddulph

"Ronnie" wrote in message
...
I am so close, but it's not working. I think it's the minus sign in the
formula. Any other suggestions.
--
Ronnie


"Ryan Martin" wrote:

the column being 'A', it could be written this way:

=COUNTIF(A:A,"<130")-COUNTIF(A:A,"<120")

"Ronnie" wrote:

I need a formula to count the number of cells in a column that contain
the
numbers between 120 and 129. Thanks for any input!
--
Ronnie

Counting # of cells
 

We may need a bit more information than "was not working".
What values did you have in the cells, which formula were you using, what
answer did you get, and what answer did you expect?
--
David Biddulph

"Ronnie" wrote in message
...
I'm not sure what was wrong. I was unable to get the counts to come out
properly. I did get the "sumproduct" formula to work. I would like to
know
why the other formula was not working, but it wasn't.
--
Ronnie


"David Biddulph" wrote:

What makes you think that the minus sign is wrong? The formula gives the
right result if the values are integer and the "between" is intended to
be
inclusive.
It would be better to use =COUNTIF(A:A,"<=129")-COUNTIF(A:A,"<120") in
case
the numbers are not necessarily integer.
Change it to =COUNTIF(A:A,"<129")-COUNTIF(A:A,"<=120") if "between" is
intended to be exclusive, not inclusive.
--
David Biddulph

"Ronnie" wrote in message
...
I am so close, but it's not working. I think it's the minus sign in the
formula. Any other suggestions.
--
Ronnie


"Ryan Martin" wrote:

the column being 'A', it could be written this way:

=COUNTIF(A:A,"<130")-COUNTIF(A:A,"<120")

"Ronnie" wrote:

I need a formula to count the number of cells in a column that
contain
the
numbers between 120 and 129. Thanks for any input!
--
Ronnie