Counting # of cells

R

Ronnie

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

Bernard Liengme

=COUNTIF(A1:A100, ">=120") - COUNTIF(A1:A100, "<=129")
or
=SUMPRODUCT(--(A1:A100>=120),--(A1:A100<=129))
best wishes
 
R

Ryan Martin

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

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

John C

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

Ronnie

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

David Biddulph

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

Ronnie

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

David Biddulph

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