 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / October 2007Count Functions
Hi Everyone i am looking for a formula that would let me count the number of cells with a value between zero and 15? is there something that would work... Thanks Try this =COUNTIF(A1:A10,"<=15") > Hi Everyone i am looking for a formula that would let me count the number of > cells with a value between zero and 15? is there something that would work... > > Thanks What if A1 = -1? > Try this > =COUNTIF(A1:A10,"<=15") [quoted text clipped - 5 lines] > > > > Thanks I guess I assumed we are working with positive numbers > What if A1 = -1? > [quoted text clipped - 7 lines] > > > > > > Thanks Does anyone know how to assign the value of 1 to a specific word that occurs throughout my sheet?  SignatureNiamh Gallagher > Try this > =COUNTIF(A1:A10,"<=15") [quoted text clipped - 3 lines] > > > > Thanks If the word exists only once per cell: =COUNTIF(A1:J100,"*specificword*") If it may exist multiple times per cell: =SUMPRODUCT(LEN(A1:J100) - LEN(SUBSTITUTE(A1:J100, "specificword", "")))/LEN("specificword") > Does anyone know how to assign the value of 1 to a specific word that occurs > throughout my sheet? Since you are assigning 1 to it you can just count it =COUNTIF(A:A,"Word") SignatureRegards, Peo Sjoblom > Does anyone know how to assign the value of 1 to a specific word that > occurs [quoted text clipped - 9 lines] >> > >> > Thanks One way, =SUMPRODUCT(--(A1:A100>=0),--(A1:A100<16)) Regards, Alan. > Hi Everyone i am looking for a formula that would let me count the number > of > cells with a value between zero and 15? is there something that would > work... > > Thanks Hmm... what if A1 = 15.5? better: =SUMPRODUCT(--(A1:A100>=0),--(A1:A100<=15)) > One way, > =SUMPRODUCT(--(A1:A100>=0),--(A1:A100<16)) [quoted text clipped - 6 lines] > > > > Thanks What if A1 is empty? =SUMPRODUCT(--(ISNUMBER(A1:A100)),--(A1:A100>=0),--(A1:A100<=15)) Although COUNTIF is best. SignatureBiff Microsoft Excel MVP > Hmm... what if A1 = 15.5? > [quoted text clipped - 11 lines] >> > >> > Thanks Better, =SUMPRODUCT(--(A1:A100>=0),--(A1:A100<=15)) Regards, > One way, > =SUMPRODUCT(--(A1:A100>=0),--(A1:A100<16)) [quoted text clipped - 6 lines] >> >> Thanks One way: =COUNTIF(A:A,">0") - COUNTIF(A:A, ">=15") Another: =SUMPRODUCT(--(A1:A1000>0),--(A1:A1000<15)) These assume "between" means non-inclusive... > Hi Everyone i am looking for a formula that would let me count the number of > cells with a value between zero and 15? is there something that would work... > > Thanks I posted my second reply before I saw yours! You put me on the right track with your original pointing out that a minus value would throw a spanner in the works, Regards, Alan. > One way: > [quoted text clipped - 12 lines] >> >> Thanks What If A1 Value = 0 > One way: > [quoted text clipped - 10 lines] > > > > Thanks "These assume "between" means non-inclusive." otherwise change the > to >= and the >= to > SignatureRegards, Peo Sjoblom > What If A1 Value = 0 > [quoted text clipped - 14 lines] >> > >> > Thanks Thanks Guys this one seemed to get the job done..... Comments from everyone were very helpful > One way: > [quoted text clipped - 10 lines] > > > > Thanks > Hi Everyone i am looking for a formula that would let me count the number of > cells with a value between zero and 15? is there something that would work... > > Thanks Function CountBetw(iRange As range, lowNum, hiNum, Optional inclLow = True, Optional inclHi = True) If inclLow = True And inclHi = True Then CountBetw = Application.CountIf(iRange, ">=" & lowNum) - Application.CountIf(iRange, ">" & hiNum) ElseIf inclLow = False And inclHi = False Then CountBetw = Application.CountIf(iRange, ">" & lowNum) - Application.CountIf(iRange, ">=" & hiNum) ElseIf inclLow = True And inclHi = False Then CountBetw = Application.CountIf(iRange, ">=" & lowNum) - Application.CountIf(iRange, ">=" & hiNum) ElseIf inclLow = False And inclHi = True Then CountBetw = Application.CountIf(iRange, ">" & lowNum) - Application.CountIf(iRange, ">" & hiNum) End If End Function Alan Beban If "between" 0 and 15 means non-inclusive 0 & 15 Then, one way, =SUM(COUNTIF(A:A,{">0",">=15"})*{1,-1}) Another way, =FREQUENCY(A1:A1000,{14,0}) Regards > > Hi Everyone i am looking for a formula that would let me count the number of > > cells with a value between zero and 15? is there something that would work... [quoted text clipped - 19 lines] > > Alan Beban > If "between" 0 and 15 means non-inclusive 0 & 15 > [quoted text clipped - 31 lines] > > > > Alan Beban > If "between" 0 and 15 means non-inclusive 0 & 15 > [quoted text clipped - 5 lines] > > =FREQUENCY(A1:A1000,{14,0}) And even this CSE way... =SUM(--(ABS(A1:A1000-7.5)<7.5)) CSE - Commit equation using Ctrl+Shift+Enter Although I guess we could make this an Enter-normal formula by replacing SUM with SUMPRODUCT. Rick > =FREQUENCY(A1:A1000,{14,0}) These NG's never cease to amaze me! SignatureRegards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk Although I haven't tried that, I've no doubt it works, and I know of your knowledge and skill over many years on these newsgroups, but with respect, isn't that a sledgehammer to crack a walnut? Regards, Alan. >> Hi Everyone i am looking for a formula that would let me count the number >> of cells with a value between zero and 15? is there something that would [quoted text clipped - 20 lines] > > Alan Beban > Although I haven't tried that, I've no doubt it works, and I know of > your knowledge and skill over many years on these newsgroups, but with > respect, isn't that a sledgehammer to crack a walnut? > Regards, > Alan. That's not my call. If you've got a handier nutcracker, by all means ignore my post. But once it's coded and in my library, and after reviewing the responses in this thread, I personally don't see much of anything easier, nor more readily committed to memory, than typing in a cell =CountBetw(a1:a100,0,15) if inclusive or =CountBetw(a1:a100,0,15,false,false) if exclusive. And the frequency with which the question arises in this newsgroup, and the qualification that always comes up in the responses about inclusive/not inclusive, suggested to me that something more intuitive and more obvious than, e.g., =SUMPRODUCT(--(ISNUMBER(A1:A100)),--(A1:A100>=0),--(A1:A100<=15)) might be useful. Also, with respect, coding such a solution allows one to think through the problem and avoid posting things like =SUMPRODUCT(--(A1:A13>=0),--(A1:A13<=15)), which, as was pointed out, counts empty cells as having values between 0 and 15; or =SUMPRODUCT(--(A1:A100>=0),--(A1:A100<16)), which has the empty cells problem and also, which was also pointed out, doesn't allow for numbers between 15 and 16. But to each his own. Alan Beban Alan I tried to use the macro posted by you. It is not working in Excel 2003. All the lines are appearing in red. I tried to straighten the line wraps, still it is not working. Are the "And" and the usage " Application.Countif "available in Excel 2003 or are they available only 2007 version ? The Help says worksheet functions should be used by coding them as Application.Worksheetfunctions.". Kindly guide me. > > Hi Everyone i am looking for a formula that would let me count the number of > > cells with a value between zero and 15? is there something that would work... [quoted text clipped - 19 lines] > > Alan Beban Try this minor modification: Public Function CountBetw( _ iRange As Range, _ lowNum As Double, _ hiNum As Double, _ Optional inclLow = True, _ Optional inclHi = True) As Variant Dim sOpLow As String Dim sOpHi As String sOp1 = IIf(inclLow, ">=", ">") sOp2 = IIf(inclHi, ">", ">=") With Application CountBetw = .CountIf(iRange, sOpLow & lowNum) - _ .CountIf(iRange, sOpHi & hiNum) End With End Function > Alan > I tried to use the macro posted by you. It is not working in Excel 2003. [quoted text clipped - 3 lines] > says worksheet functions should be used by coding them as > Application.Worksheetfunctions.". Kindly guide me. > Try this minor modification: > [quoted text clipped - 13 lines] > End With > End Function Or, as a "one-liner"... Public Function CountBetw( _ iRange As Range, _ lowNum As Double, _ hiNum As Double, _ Optional inclLow = True, _ Optional inclHi = True) As Variant With Application CountBetw = .CountIf(iRange, ">" & String(-inclLow, "=") & lowNum) - _ .CountIf(iRange, ">" & String(-inclHi, "=") & hiNum) End With End Function Rick > Or, as a "one-liner"... > [quoted text clipped - 11 lines] > > Rick That doesn't seem to work when the range includes 0 or 15 Alan >> Or, as a "one-liner"... >> [quoted text clipped - 13 lines] > > That doesn't seem to work when the range includes 0 or 15 Yes, I think I got the inclusion of the equal sign backward on the second COUNTIF. I think this should work correctly... Public Function CountBetw( _ iRange As Range, _ lowNum As Double, _ hiNum As Double, _ Optional inclLow = True, _ Optional inclHi = True) As Variant With Application CountBetw = .CountIf(iRange, ">" & String(-inclLow, "=") & lowNum) - _ .CountIf(iRange, ">" & String(-Not (inclHi), "=") & hiNum) End With End Function Rick >> That doesn't seem to work when the range includes 0 or 15 > [quoted text clipped - 14 lines] > > Rick Seems to. Thanks to you and JEM for the input. Alan > Try this minor modification: > [quoted text clipped - 13 lines] > End With > End Function That seems to work if you change sOp1 to sOpLow and sOp2 to sOpHi :-) Alan Oops - that's what I get for trying to make it more readable once I put it in a news article. Thanks for the correction! > That seems to work if you change sOp1 to sOpLow and sOp2 to sOpHi :-) Since all the lines are red, my guess is that you pasted in the > characters that precede each line of the posting in the newsgroup. If you simply paste exactly what is not working in a posting in this thread, that will tell us the problem. Incidentally, the problem is NOT the Application.WorksheetFunctions discrepancy, which has only limited ramifications not applicable here. Alan Beban > Alan > I tried to use the macro posted by you. It is not working in Excel 2003. [quoted text clipped - 27 lines] >> >>Alan Beban Alan Thanks. It was not the > character. I tried to remove the spaces in the beginning of each line, all except the following have turned black. ElseIf inclLow = True And inclHi = False Then CountBetw = Application.CountIf(iRange, ">=" & lowNum) _ Application.CountIf(iRange, ">=" & hiNum) The elseif inclLow line is OK. The subsequent two are still in red. I am not able to correct it. > Since all the lines are red, my guess is that you pasted in the > > characters that precede each line of the posting in the newsgroup. If [quoted text clipped - 36 lines] > >> > >>Alan Beban Alan, Thanks. It was not the > character. I removed the empty spaces in the beginning of each line, which turned the lines black. One "-" sign was missed by me while copying, which I have found out and rectified. It's now working. Thanks once again. > Since all the lines are red, my guess is that you pasted in the > > characters that precede each line of the posting in the newsgroup. If [quoted text clipped - 36 lines] > >> > >>Alan Beban Thanks for the feedback. It's always a bit worrisome when something posted doesn't work for the recipient. Good luck, Alan Beban > Alan, > Thanks. It was not the > character. I removed the empty spaces in the > beginning of each line, which turned the lines black. One "-" sign was missed > by me while copying, which I have found out and rectified. It's now working. > Thanks once again. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.