A
Amy
I would like to count the number of cells in a range that has a number in it
that is greather than 4999 but less than 9999 - any ideas
that is greather than 4999 but less than 9999 - any ideas
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
R
Roger Govier
Hi Amy
One way, with data in column A, enter in B1
=COUNTIF(A:A,">4999")-COUNTIF(A:A,">9999")
One way, with data in column A, enter in B1
=COUNTIF(A:A,">4999")-COUNTIF(A:A,">9999")
G
Gary''s Student
=ABS(COUNTIF(A:A,">" & 4999)-COUNTIF(A:A,">" & 9998))
A
Amy
Roger,
Thanks for the advice, what that equation is doing is counting the numbers
less than 4999 and subtracting the numbers that are greater than 9999
Thanks for the advice, what that equation is doing is counting the numbers
less than 4999 and subtracting the numbers that are greater than 9999
D
David Biddulph
No, Amy, it is counting the numbers *greater than* 4999 (not less than), and
subtracting those greater than 9999. The result is those which are greater
than 4999 and less than or equal to than 9999.
As you wanted greater than 4999 and *less than* 9999, then in Roger's
formula I would change ,">9999" to ,">=9999"
subtracting those greater than 9999. The result is those which are greater
than 4999 and less than or equal to than 9999.
As you wanted greater than 4999 and *less than* 9999, then in Roger's
formula I would change ,">9999" to ,">=9999"
T
TRYING
AMY: You can see, just like I did, the wisdom and accuracy of David's reply
by testing it with a small sample. What I did is enter 4998, 4999, 5000,
9998, 9999, 10000, and 10001 into cells A1 to A7.
With this small sample, you can easily see that there is only 1 number *less
than* 4999 while there are 2 number *greater than* 9999. If Roger's formula
(as corrected by David) was doing what you said it was, it would return -1.
In contrast, it is correctly returning 2 representing the numbers 5000 and
9998, the two numbers in the sample that meet both conditions of being
greater than 4999 AND less than 9999.
Alternatively, you can also simply change substitute 9998 for 9999 in
Roger's formula and you would get the same results as David proposed.
Another way of looking at this is the formula is counting all numbers in
column A that are greater than 4999 (which in my sample is 5) and subtracting
from that the count of numbers that are greater than 9998 (which in my sample
is 3) returning a result of 2.
I know this is very verbose but I'm really doing this to personally learn
this stuff. I find that one of the best ways to test whether I have learned
something is trying to explain it to someone else. As my username suggests,
I'm just TRYING to learn. I learn a lot about logic and thinking outside the
box from these guys.
by testing it with a small sample. What I did is enter 4998, 4999, 5000,
9998, 9999, 10000, and 10001 into cells A1 to A7.
With this small sample, you can easily see that there is only 1 number *less
than* 4999 while there are 2 number *greater than* 9999. If Roger's formula
(as corrected by David) was doing what you said it was, it would return -1.
In contrast, it is correctly returning 2 representing the numbers 5000 and
9998, the two numbers in the sample that meet both conditions of being
greater than 4999 AND less than 9999.
Alternatively, you can also simply change substitute 9998 for 9999 in
Roger's formula and you would get the same results as David proposed.
Another way of looking at this is the formula is counting all numbers in
column A that are greater than 4999 (which in my sample is 5) and subtracting
from that the count of numbers that are greater than 9998 (which in my sample
is 3) returning a result of 2.
I know this is very verbose but I'm really doing this to personally learn
this stuff. I find that one of the best ways to test whether I have learned
something is trying to explain it to someone else. As my username suggests,
I'm just TRYING to learn. I learn a lot about logic and thinking outside the
box from these guys.