Hello and thank you for looking at my problem. I have a column of
numbers say 1 to 100 (A:1 to A:100) and I want to count the number of
cells in that range that are between 30 and 50 and including 30 and 50
(count should be 21). I've tried most count functions but can't seem to
come up with the right formula. Can someone please help. Thanks John
:)

=SUMPRODUCT((A1:A100=30)*(A1:A100<=50))

Tyro

"Johnny1r" wrote in message
...
Hello and thank you for looking at my problem. I have a column of
numbers say 1 to 100 (A:1 to A:100) and I want to count the number of
cells in that range that are between 30 and 50 and including 30 and 50
(count should be 21). I've tried most count functions but can't seem to
come up with the right formula. Can someone please help. Thanks John
:)

Thank you , it works great. John :)


Tyro wrote:

=SUMPRODUCT((A1:A100=30)*(A1:A100<=50))

Use to COUNTIFs...

=COUNTIF(A1:A100,"<=50")-COUNTIF(A1:A100,"<30")

Notice the logical operators... the one in front of the 50 has the equal
sign because we want to count the 50, the second one omits the equal sign
because it is what is being subtracted and we do not want to subtract the
30.

Rick


"Johnny1r" wrote in message
...
Hello and thank you for looking at my problem. I have a column of
numbers say 1 to 100 (A:1 to A:100) and I want to count the number of
cells in that range that are between 30 and 50 and including 30 and 50
(count should be 21). I've tried most count functions but can't seem to
come up with the right formula. Can someone please help. Thanks John
:)

Use to COUNTIFs...

"to"???? LOL... Of, course, that should have been "two".

Rick

Thanks Rick , this one works great also. You guys are great!!!
Thanks again, John :)


Rick Rothstein (MVP - VB) wrote:

Use to COUNTIFs...

=COUNTIF(A1:A100,"<=50")-COUNTIF(A1:A100,"<30")

Notice the logical operators... the one in front of the 50 has the
equal sign because we want to count the 50, the second one omits the
equal sign because it is what is being subtracted and we do not want
to subtract the 30.

Rick


"Johnny1r" wrote in message
...
Hello and thank you for looking at my problem. I have a column of
numbers say 1 to 100 (A:1 to A:100) and I want to count the number
of cells in that range that are between 30 and 50 and including 30
and 50 (count should be 21). I've tried most count functions but
can't seem to come up with the right formula. Can someone please
help. Thanks John :)