A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

if then statement



 
 
Thread Tools Display Modes
  #1  
Old January 8th, 2008, 10:03 PM posted to microsoft.public.excel.misc
Amy
external usenet poster
 
Posts: 412
Default 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  
Old January 8th, 2008, 10:18 PM posted to microsoft.public.excel.misc
Roger Govier[_3_]
external usenet poster
 
Posts: 2,297
Default 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  
Old January 8th, 2008, 10:20 PM posted to microsoft.public.excel.misc
Gary''s Student
external usenet poster
 
Posts: 7,584
Default 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  
Old January 8th, 2008, 10:29 PM posted to microsoft.public.excel.misc
Amy
external usenet poster
 
Posts: 412
Default 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  
Old January 8th, 2008, 11:18 PM posted to microsoft.public.excel.misc
David Biddulph
external usenet poster
 
Posts: 8,714
Default 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  
Old January 9th, 2008, 12:51 AM posted to microsoft.public.excel.misc
Trying
external usenet poster
 
Posts: 49
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:32 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.