Greater Than / Less Than Problem

Don't want to sound silly here, but I am actually having a problem with
a formula that will allow me to count only values greater than 0 but
less than 11.  Is this Possible in 1 unique formula?  Heres the
situation:

Within Column B6:B35,  I want a count of any number that is greater
than 0 but less than 11.   I tried the following but it returns a 0:

countif(B6:B35,">0<11")

Thanks In Advance for any help here...


-- 
Mhz
------------------------------------------------------------------------
Mhz's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=35980
View this thread: http://www.excelforum.com/showthread.php?threadid=561009

0
7/13/2006 9:27:51 AM
excel.newusers 15348 articles. 2 followers. Follow

11 Replies
535 Views

Similar Articles

[PageSpeed] 27

> countif(B6:B35,">0<11")

One way, try:
=COUNTIF($B$6:$B$35,">0")-COUNTIF($B$6:$B$35,">=11")
-- 
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Mhz" wrote:
> 
> Don't want to sound silly here, but I am actually having a problem with
> a formula that will allow me to count only values greater than 0 but
> less than 11.  Is this Possible in 1 unique formula?  Heres the
> situation:
> 
> Within Column B6:B35,  I want a count of any number that is greater
> than 0 but less than 11.   I tried the following but it returns a 0:
> 
> countif(B6:B35,">0<11")
> 
> Thanks In Advance for any help here...
> 
> 
> -- 
> Mhz
> ------------------------------------------------------------------------
> Mhz's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=35980
> View this thread: http://www.excelforum.com/showthread.php?threadid=561009
> 
> 
0
demechanik (4694)
7/13/2006 9:45:02 AM
Another way:
=SUMPRODUCT((B6:B35>0)*(B6:B35<=11))

Cheers,
--
AP

"Max" <demechanik@yahoo.com> a �crit dans le message de news: 
66DDBE74-072F-46B3-8187-069E744AE1C2@microsoft.com...
>> countif(B6:B35,">0<11")
>
> One way, try:
> =COUNTIF($B$6:$B$35,">0")-COUNTIF($B$6:$B$35,">=11")
> -- 
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---
> "Mhz" wrote:
>>
>> Don't want to sound silly here, but I am actually having a problem with
>> a formula that will allow me to count only values greater than 0 but
>> less than 11.  Is this Possible in 1 unique formula?  Heres the
>> situation:
>>
>> Within Column B6:B35,  I want a count of any number that is greater
>> than 0 but less than 11.   I tried the following but it returns a 0:
>>
>> countif(B6:B35,">0<11")
>>
>> Thanks In Advance for any help here...
>>
>>
>> -- 
>> Mhz
>> ------------------------------------------------------------------------
>> Mhz's Profile: 
>> http://www.excelforum.com/member.php?action=getinfo&userid=35980
>> View this thread: 
>> http://www.excelforum.com/showthread.php?threadid=561009
>>
>> 


0
ardus.petus (319)
7/13/2006 9:59:16 AM
I meant <11, not <=11!
Sorry,
--
AP

"Max" <demechanik@yahoo.com> a �crit dans le message de news: 
66DDBE74-072F-46B3-8187-069E744AE1C2@microsoft.com...
>> countif(B6:B35,">0<11")
>
> One way, try:
> =COUNTIF($B$6:$B$35,">0")-COUNTIF($B$6:$B$35,">=11")
> -- 
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---
> "Mhz" wrote:
>>
>> Don't want to sound silly here, but I am actually having a problem with
>> a formula that will allow me to count only values greater than 0 but
>> less than 11.  Is this Possible in 1 unique formula?  Heres the
>> situation:
>>
>> Within Column B6:B35,  I want a count of any number that is greater
>> than 0 but less than 11.   I tried the following but it returns a 0:
>>
>> countif(B6:B35,">0<11")
>>
>> Thanks In Advance for any help here...
>>
>>
>> -- 
>> Mhz
>> ------------------------------------------------------------------------
>> Mhz's Profile: 
>> http://www.excelforum.com/member.php?action=getinfo&userid=35980
>> View this thread: 
>> http://www.excelforum.com/showthread.php?threadid=561009
>>
>> 


0
ardus.petus (319)
7/13/2006 10:00:37 AM
My Goodness, what a wierd Formula... But YES!  it definately works!   

Thanks Max,   Much Appreciated!   (I still dont understand the >= valu
on the last formula, but it actually works well.  You would assume i
would contain a <= value)  but who cares, IT WORKS!   thanks..:

--
Mh
-----------------------------------------------------------------------
Mhz's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3598
View this thread: http://www.excelforum.com/showthread.php?threadid=56100

0
7/13/2006 10:03:25 AM
One way I know to accomplish this is by placing a Zero (0) in any unuse
out of the way cell. Then copy and paste this formula in what ever cel
you want the total in.

=COUNTIF($B$6:$B$35,">Z1")+COUNTIF($B$6:$B$35,"<11")

Z1 is the cell I chose to put the (0) in. Hope this helps.

E

--
patel
-----------------------------------------------------------------------
patele's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3584
View this thread: http://www.excelforum.com/showthread.php?threadid=56100

0
7/13/2006 10:11:33 AM
Hi

As an alternative to Countif you could use the Sumproduct function
=SUMPRODUCT(--(INT(B6:B35/12)=0),B6:B35)

Taking the Integer of the numbers in your range divided by 12, will 
return 0 for all values below 12, and 1 for any values greater than 11.
The first part of the test will therefore return True when less than or 
equal to 11, and False when greater than 11.
The double unary minus -- coerces these True's to 1 and False's to 0.
Sumproduct then multiplies each of the values in your range by 1 or 0 
and sums the results where all values outside the range 0 to 11 will 
have been converted to 0.

-- 
Regards

Roger Govier


"Max" <demechanik@yahoo.com> wrote in message 
news:66DDBE74-072F-46B3-8187-069E744AE1C2@microsoft.com...
>> countif(B6:B35,">0<11")
>
> One way, try:
> =COUNTIF($B$6:$B$35,">0")-COUNTIF($B$6:$B$35,">=11")
> -- 
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---
> "Mhz" wrote:
>>
>> Don't want to sound silly here, but I am actually having a problem 
>> with
>> a formula that will allow me to count only values greater than 0 but
>> less than 11.  Is this Possible in 1 unique formula?  Heres the
>> situation:
>>
>> Within Column B6:B35,  I want a count of any number that is greater
>> than 0 but less than 11.   I tried the following but it returns a 0:
>>
>> countif(B6:B35,">0<11")
>>
>> Thanks In Advance for any help here...
>>
>>
>> -- 
>> Mhz
>> ------------------------------------------------------------------------
>> Mhz's Profile: 
>> http://www.excelforum.com/member.php?action=getinfo&userid=35980
>> View this thread: 
>> http://www.excelforum.com/showthread.php?threadid=561009
>>
>> 


0
roger5293 (1125)
7/13/2006 10:12:55 AM
My apologies
Ardus' solution only appeared after posting mine (far easier logic) and 
I noticed his correction to <11.
I too had misread your posting and assumed you wanted to include 11.
My formula would need to be modified to use INT(B6:B35/11)

-- 
Regards

Roger Govier


"Roger Govier" <roger@technologyNOSPAM4u.co.uk> wrote in message 
news:%23D8yrTmpGHA.2460@TK2MSFTNGP03.phx.gbl...
> Hi
>
> As an alternative to Countif you could use the Sumproduct function
> =SUMPRODUCT(--(INT(B6:B35/12)=0),B6:B35)
>
> Taking the Integer of the numbers in your range divided by 12, will 
> return 0 for all values below 12, and 1 for any values greater than 
> 11.
> The first part of the test will therefore return True when less than 
> or equal to 11, and False when greater than 11.
> The double unary minus -- coerces these True's to 1 and False's to 0.
> Sumproduct then multiplies each of the values in your range by 1 or 0 
> and sums the results where all values outside the range 0 to 11 will 
> have been converted to 0.
>
> -- 
> Regards
>
> Roger Govier
>
>
> "Max" <demechanik@yahoo.com> wrote in message 
> news:66DDBE74-072F-46B3-8187-069E744AE1C2@microsoft.com...
>>> countif(B6:B35,">0<11")
>>
>> One way, try:
>> =COUNTIF($B$6:$B$35,">0")-COUNTIF($B$6:$B$35,">=11")
>> -- 
>> Max
>> Singapore
>> http://savefile.com/projects/236895
>> xdemechanik
>> ---
>> "Mhz" wrote:
>>>
>>> Don't want to sound silly here, but I am actually having a problem 
>>> with
>>> a formula that will allow me to count only values greater than 0 but
>>> less than 11.  Is this Possible in 1 unique formula?  Heres the
>>> situation:
>>>
>>> Within Column B6:B35,  I want a count of any number that is greater
>>> than 0 but less than 11.   I tried the following but it returns a 0:
>>>
>>> countif(B6:B35,">0<11")
>>>
>>> Thanks In Advance for any help here...
>>>
>>>
>>> -- 
>>> Mhz
>>> ------------------------------------------------------------------------
>>> Mhz's Profile: 
>>> http://www.excelforum.com/member.php?action=getinfo&userid=35980
>>> View this thread: 
>>> http://www.excelforum.com/showthread.php?threadid=561009
>>>
>>>
>
> 


0
roger5293 (1125)
7/13/2006 10:23:15 AM
"Mhz" wrote:
> My Goodness, what a weird Formula... But YES!  it definitely works!   
> Thanks Max,   Much Appreciated!   (I still dont understand the >= value
> on the last formula, but it actually works well.  You would assume it
> would contain a <= value) but who cares, IT WORKS!   

You're welcome. 

We're simply slicing off the part we don't want (>=11) 
from the returns by the 1st countif: COUNTIF($B$6:$B$35,">0")
via subtracting it with the 2nd one: COUNTIF($B$6:$B$35,">=11")
(visualize it as a number line ..)
-- 
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
0
demechanik (4694)
7/13/2006 10:30:02 AM
Thanks For All The Replies!!   Roger, That is a Brain Digging Formul
But surely Works..:)    

And Yes, Max Explained about the >=11 Factor, (Cutting of anthing Abov
11), WORKS GREAT!

Thanks to all of you..  More than one way to get a good result, fro
simple to complex...GOOD DEAL! ;

--
Mh
-----------------------------------------------------------------------
Mhz's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3598
View this thread: http://www.excelforum.com/showthread.php?threadid=56100

0
7/13/2006 10:57:26 AM
> .. GOOD DEAL!

ay, that's what one always get around here <g>
-- 
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
0
demechanik (4694)
7/13/2006 11:23:01 AM
Actually, your formula will count all of the values in the range, with the 
values between 0 and 11 counted twice.

">Z1" s/b ">"&Z1


"patele" wrote:

> 
> One way I know to accomplish this is by placing a Zero (0) in any unused
> out of the way cell. Then copy and paste this formula in what ever cell
> you want the total in.
> 
> =COUNTIF($B$6:$B$35,">Z1")+COUNTIF($B$6:$B$35,"<11")
> 
> Z1 is the cell I chose to put the (0) in. Hope this helps.
> 
> Ed
> 
> 
> -- 
> patele
> ------------------------------------------------------------------------
> patele's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=35849
> View this thread: http://www.excelforum.com/showthread.php?threadid=561009
> 
> 
0
jmb (270)
7/13/2006 7:10:02 PM
Reply:

Similar Artilces:

Problem running Money Plus Deluxe
I just purchased and loaded Money Plus Deluxe. The program will not respond after I enter my password. It seems to accept the password but when I go to Task Manager, it shows the program is not responding. I tried deleting the program and loaded it again but that didn't work. Any suggestions? What O/S? -- Regards Bob Peel, Microsoft MVP - Money For unofficial FAQs see http://money.mvps.org/ or http://umpmfaq.info/ I do not respond to any emails that I have not specifically asked for. "Poke stuck in Kuwait" <PokestuckinKuwait@discussions.microsoft.com> wrote in...

Balancing problems?
I have no problems with my other accounts. So I know I can use this software properly and balance my accounts. However, with one account the balance is always off by $7200 (in some places). The weird part is that the balance is correct in the register and matches what my bank statement says. But I look just below in the cash flow forecast and there is the $7200 discrepancy. When I balance the account, every month it wants to make a $7200 correction. If I do that, the balance in the register is now off by $7200. So even the balances shown on the same page do not match. Now if ...

problem with wrap text
I tried looking all through the newsgroup and saw nothing that approaches my query. I do a lot of data editing, working through other people's stuff and trying to standardize it, and that entails a lot of global replaces. It is s.o.p. for me to set |Format|Alignment|Wrap Text| to off, to compact a worksheet and to determine when someone decided to write a book where a single word is needed. Somehow, the Wrap keeps coming back! I have a suspicion that there is some sort of option or default which has got changed, but I know not where or what I have done to deserve this! Any advice...

Problem starting Outlook
I just updated to outlook 2003 yesterday. Everytime I quit Outlook, It will not restart unless I reboot, or delete Outlook.exe from processes in task manager. I am having the same problem with Outlook Express installed on my new Dell. I can open it sometimes and then when I switch to a different identity it refuses to switch, then will not open at all unless I reboot. >-----Original Message----- >I just updated to outlook 2003 yesterday. Everytime I >quit Outlook, It will not restart unless I reboot, or >delete Outlook.exe from processes in task manager. >. > ...

2002 attachment problems
I am using a dial up modem to send emails. A few weeks ago, I became unable to send jpeg attachments properly. The task bar will say Send/Recieve status 40%. Then it goes to 60%. A few seconds later it goes back down to 40%. Eventually, I have to delete the email from my outbox. It never shows as sent. Meanwhile, the recipient gets about 10 copies of the message with the attachments. I have sent many emails in the past, with no problem. I look forward to any help I can get. ...

Outlook 2007 attachment problem
Hi, I have tried to get help on this problem for a month or so, on different forums, but there is no-one who can help me. I came across this forum today and am hoping that some who understand Outlook 2007 inside out can help me. I have an email in the drafts folder which is 480MB in size. It contains all my emails from my inbox in one attachment. I don't know how it happened, but I need to get those emails back from the attachment and put them back in my inbox. Everytime I try to open the attachment, my outlook grinds to a halt. I can only 'open' it when I run...

Problem with vba directory function
Hi We have an access application that has a button when clicked opens an excel worksheet showing all exported data based on code The access sql system is in Citrix with a single front end copy. Here is the code part that is throwing the error for just one user. Rest all are working fine Dim strSourceFileName As String Dim WorkBookName As String strSourceFileName = "c:\CarLogOutPut\CarLogExportTemplate.xls" WorkBookName = "c:\CarLogOutPut\CarLogExport.xls" If Dir(strSourceFileName) = "" Then MsgBox "CarLogExportTemplate.xls doe...

Problem re-installing on new Macbook
Version: v.X Operating System: Mac OS X 10.5 (Leopard) Processor: intel Hi There, I just purchased a new Macbook Pro and am trying to re-install all the old apps from my earlier powerbook. I have a problem with installing Office v.X because after all these years I only have the v.X upgrade CD - I can't locate the Office 98 Upgrade and original Word for Mac CDs/Disks because its been such a long time! When I do the install it asks for the location of the earlier install. Is there any way to work around this? I have receipts for the purchase of Office v.X upgrade and Office 98 upgrade for...

XL 2K Paste special problems
I have a client with Excel 2000 (Win NT). Her PC just got rebuilt and now when she does a paste special in Excel instead of seeing a choice of all, formulas, etc... she sees options like you'd see in Word (i.e. object, etc...). Does anyone know of an option that might fix that? In the meantime I've asked for a reinstall, but if I could save them the work, they'd appreciate it. Thanks, CB Sounds like she didn't COPY cells, but perhaps copied a bitmap image or a web part,and Excel couldn't paste non-excel source. Try this to see if Excel is working "OK": sel...

.pst installation problem
I just loaded OL on my lap and tranfered the .pst from my dektop. All the info loaded, but when I shut down and reopened OL, it locked up at the license screen. I can't do anything from inside the program. I assume the problem is with the .pst import, but I can't find the .pst file on my hard drive to delete it. Any suggestions on what I can do to get OL to open fully? Thanks. You may have a corrupted outcmd.dat file. This is the file that stores your toolbar customizations, and when it becomes corrupted, Outlook can't use it at startup to build your toolbars, thus causing t...

Please help me solve my problem
Hi, I Have a cell say a1 that has a continuously changing number it in and I want to track the highest number that it prints, and the same for the lowest number in cell a2. I would like these results to show up in the cells below them Im very new to programming and your help would be much appreciated many thanks Sacha David On Mar 5, 1:01=A0pm, Sacha David <sach1...@googlemail.com> wrote: > Hi, =A0I Have a cell say a1 that has a continuously changing number it > in and I want to track the highest number that it prints, =A0and the > same for the lowest number i...

Connection Problem 03-20-08
Hello I'm new to access programming so I'm probably making some obvious mistake. The following code comes from 'Beginning Access 2003 VBA' by Denise Cosnell. On page 130 of the book you can read: 1. Create a new database by selecting File ?New ?Blank Database and specifying Ch5CodeExamples as the filename. 2. Create a new table in the database by selecting Tables from the Database Window and clicking the Design button. Alternatively, you can select the New button and then choose Design View from the list. The table should be named tblContacts and should have the fields i...

problem with IXMLDOMDocument's method loadXML
I'm trying to parse XML file using MSXML. This is my code: //first establishing connection, downloading xml file from http server and store it in CHttpFile object (pFile) // (...) CFile file; file.Open("c:\\test.xml", CFile::OpenFlags::modeRead,0); int size = file.GetLength(); char *str = new char[size+1]; file.Read(str, file.GetLength()+1); file.Close(); _variant_t varOut((bool)TRUE); varOut = pDomDocument->loadXML(str); if ((bool)varOut == TRUE) { //parse the document } else { MSXML2::IXMLDOMParseErrorPtr errPtr = pDomDocument->GetparseError(); CString err...

Contact recognition problem
I have copied my OL2007 PST file to a new computer but I cant get OL2007 on the new computer to recognise my contact folders. (Address books) The option in contacts properties, etc.. to make them an Outlook address book is greyed out. I have two such contact/address books but I can only see/locate one. Any assistance would be appreciated. Andy Try a new mail profile and connect it to your .pst file you copied. -- Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. ALWAYS post your Outlook version. How to ask a question: http://support.microsoft...

Problems Launching Outlook (2002)
When I launch Outlook...it seems to freeze up on me. Takes several minutes to load....once loaded I can send & receive e-mails w/ out problem. However, the launching is driving me nuts...my in/out server settings are correct.....Any suggestions? Thanks for any assistance... Steve Close Outlook. Find and rename outcmd.dat to .old. (If using Windows 2000 or XP, you will need to enable searching hidden and system folders.) Once you have done so, reopen Outlook and it should open fine. You will need to rebuild any custom toolbars or customizations as the file outcmd.dat controls...

dropdown list problem for newbie
i have a column(A) where each cell is a dropdownlist(same list). if person chooses an item from that list...can it return a correspondin value? example: my drop down list corresponding value animal 005 building 1174 vehicle 3345 so for a specific cell...lets say A1, i choose building from th dropdown. i want it to return a value of 1174 in cell A1. is this possible? if so please help? thx eri -- enag ----------------------------------------------------------------------- enags'...

Problem sending emails
Hi In OL2000 I am getting 'no mail transport provider...' error. I have looked in ms kb but the possible reasons for this are enormous. Is there a way to narrow down the real reason? Should I upgrade to ol2003? Would that help this problem in anyway? Thanks Regards John, You are most likely getting a number of hits on this as it can be caused by various issue. I can only suggest that you read the article included and try to deduct which one(s) may pertain to you. http://support.microsoft.com/?id=197417 Regards -- Francine Otterson President, San Diego Outlook User Group ...

Problem moving mailbox between administrative groups
Hi there, We have a big problem. We need to move mailboxes from one adm group to other, and at ramdom we receive a MAPI Error Error moving messages CN=testuser,CN=users,dc=machine,dc=company,dc=com The MAPI call failed MAPI or an unspecified service provicer ID no: 80004005-0000-00000000 The Exchange 2000 organization is in native mode (All E2K) Any thoughts. Thanks in advance -- Gabriel N Argentina ...

Copy / Paste of Floorplan shapes in Visio Automation problems
Hi All I am working on a .Net (FW1.1) application that has a hosted Visio drawing control. Copy and paste from most existing Visio drawings to this hosted control works fine. The exception to this rule is Visio floorplans, where copying and pasting of most shapes is OK, but walls go totally screwed up (all over the screen, nothing like their original shape, etc). Our client needs to be able to copy and paste the floorplans into this container control without the walls breaking; they have a large investment already made i.t.o. existing floorplans. Page scaling and size is the same as the exi...

IHTMLDocument::Write method problem
Hello EveyOne I am using IHTMLdocument and its method. My aim is to extract the body of a IHTMLDocument after loading the local html page ( saved file ) to IHTMDocument. So I just read the file into buffer , created a safe Array and used IHTMLDocument::write method to write the contents of a file to IHTMLDocument. It worked fine for many web pages, But when I tried it for web page http://www.microsoft.com/isapi/redir.dll?prd=ie&ar=windowsmedia application became NOt Responding.When I debugged the application , I found that the IHTMLDocument::write method never returned and thats why my ap...

Exchange/Outlook security problems Password What??
Hello everyone, I hope one of you Exchange/Outlook users out there can help solve this problem. This has all started out by needing to turn off the "Something's looking at your email addresses" nag box in Outlook 2003, in order to get a macro written in Outlook 2003 to work properly. According to what I have read and hopefully understood from Microsoft's support documents ("Customizing the Outlook Security Features Administrative Package" is that these nag boxes can be bypassed with the Outlook Security Features Administrative Package. I have set up a form in a ...

Problem with symbols
Hello, I'm trying to create some shortcuts to enter Greek letters into cells in Excel. The =CHAR() works for most characters, but the unicode/hex for the Greek letters is 03BB (lambda, for example) but nothing recognizes the alpha characters. If I go to the symbol font I can enter l for lambda, but I'd like something fast and easy, and not have to go to the mouse to change fonts. I tried a macro that changes fonts, but it doesn't work while typing in a cell...it changes it, and then everything that then gets typed into the cell is that font. That isn't normally what I want. A...

Problems consuming a webservice in Excel2003
Hi, I am not a MS-Office expert but have been tasked with trying something new to me. If there is a better place to ask this question, please feel free to point me to it. I have built a Webservice in Delphi 2007. It works fine with a client built in D2007 and with a generic webbased client. Now I would like to consume it using Excel 2003. The final goal is to fill a statistics spreadsheet with a set of indicators calculated from inside a separate system. I have installed the Web Services Toolkit and followed the quick walkthrough in: http://weblogs.asp.net/erobillard/archive/2005/07...

Money 2002 backup restore problem
I had a corrupt system file (or something) that required me to reformat my hard drive. I always backed up my Money file to a floppy disk on a routine basis. When I tried to restore the file I received an error that the file "was corrupt or not a Money backup file." I'm pretty certain the file is not corrupt and this is a problem with the restore process. Anyone have a similar problem? (Yes, I've read all the procedures on how to do it.) TIA, Dave Many people who get themselves in this situation installed a different version of Money than the one they were actually u...

sharing problem
Hi there, I have a strange problem. I have 13 exchange users an Exchange 2000 (english) All the users were migrated when I installed Exchange 1 year ago. a month ago 2 users left and were replaced by 2 others so I added them to exchange. All users are using Outlook 2000 (dutch) and are sharing their calendar. When I share the calendar of the 2 new people everybody gets the share in their outlook but when they open it it gives an error that it can't open the share. The strange thing is also that thier shares are in dutch (postbus) and all other shares are in english. I made a workaround b...