If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
if then statement
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 |
#2
|
|||
|
|||
if then statement
Hi Amy
One way, with data in column A, enter in B1 =COUNTIF(A:A,"4999")-COUNTIF(A:A,"9999") -- Regards Roger Govier "Amy" wrote in message ... 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 |
#3
|
|||
|
|||
if then statement
=ABS(COUNTIF(A:A,"" & 4999)-COUNTIF(A:A,"" & 9998))
-- Gary''s Student - gsnu200764 "Amy" wrote: 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 |
#4
|
|||
|
|||
if then statement
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 "Roger Govier" wrote: Hi Amy One way, with data in column A, enter in B1 =COUNTIF(A:A,"4999")-COUNTIF(A:A,"9999") -- Regards Roger Govier "Amy" wrote in message ... 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 |
#5
|
|||
|
|||
if then statement
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" -- David Biddulph "Amy" wrote in message ... 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 "Roger Govier" wrote: Hi Amy One way, with data in column A, enter in B1 =COUNTIF(A:A,"4999")-COUNTIF(A:A,"9999") -- Regards Roger Govier "Amy" wrote in message ... 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 |
#6
|
|||
|
|||
if then statement
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. "David Biddulph" wrote: 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" -- David Biddulph "Amy" wrote in message ... 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 "Roger Govier" wrote: Hi Amy One way, with data in column A, enter in B1 =COUNTIF(A:A,"4999")-COUNTIF(A:A,"9999") -- Regards Roger Govier "Amy" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|