 |
 | 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 / May 2008Counting cells in a range per multiple criteria . . .
Hello, This is probly a simple thing to do but I cant figure it out . . . I have a coulumn that is full of dates and I want to count how many cells fall within a certain range of dates. For example : A1 = 5/1/08 A2 = 5/5/08 A3 = 5/12/08 A4 = 5/14/08 A5 = 5/25/08 I want to how many cells in this range are earlier than or equal to 5/25/08 but later than or equal to 5/12/08. Should be 3 but what would the formula look like to calculate that? Thanks! Try this: C1 = 5/12/2008 D1 = 5/25/2008 =COUNTIF(A1:A5,">="&C1)-COUNTIF(A1:A5,">"&D1) Format as GENERAL or NUMBER  SignatureBiff Microsoft Excel MVP > Hello, > This is probly a simple thing to do but I cant figure it out . . . [quoted text clipped - 12 lines] > > Thanks! Hi Biff, Just jumping in... I tried your formula - it works, of course :) Could you please explain to me why the operators need to be in double quotes, and why we need the & thingy? Regards - Dave. > Try this: > [quoted text clipped - 4 lines] > > Format as GENERAL or NUMBER "Why" is a hard question to answer in this case! I don't know "why" other than to say that's how the programmers that developed the formula parser wrote it to work. When using a comparison operator and referring to a reference (which might also be another function) You have to concatenate the operator to the reference (with certain functions, COUNTIF being one of those): =COUNTIF(A1:A5,">="&C1) =COUNTIF(A1:A5,">="&DATE(2008,5,12)) If you tried this: =COUNTIF(A1:A5,">=C1") Then it evaluates ">=C1" as the literal TEXT string >=C1 But, if the comparison criteria is a hard coded constant you just enclose both the operator and criteria in quotes (although concatenation will still work): =COUNTIF(A1:A5,">=10") =COUNTIF(A1:A5,">="&10) When testing for equality then no operator is required but it still works if you do include it: =COUNTIF(A1:A10,10) =COUNTIF(A1:A10,"=10") =COUNTIF(A1:A10,"="&10) These are nuances of Excel that you learn and get used to over time! SignatureBiff Microsoft Excel MVP > Hi Biff, > Just jumping in... [quoted text clipped - 11 lines] >> >> Format as GENERAL or NUMBER Hi Biff, Thanks for your detailed reply. Countif is amazingly useful, and it's good to know its "nuances" Regards - Dave. > "Why" is a hard question to answer in this case! > [quoted text clipped - 29 lines] > > These are nuances of Excel that you learn and get used to over time! You're welcome. Thanks for the feedback! SignatureBiff Microsoft Excel MVP > Hi Biff, > Thanks for your detailed reply. Countif is amazingly useful, and it's good [quoted text clipped - 37 lines] >> >> These are nuances of Excel that you learn and get used to over time! Fellas thanks a lot that will help me out! Dan > You're welcome. Thanks for the feedback! > [quoted text clipped - 39 lines] > >> > >> These are nuances of Excel that you learn and get used to over time! Ok I have another question with this same thing here . . . Say the dates in A1 thru A5 remain the same. I add the following column : E1 = "Done" E2 = "Open" E3 = "Open" E4 = "Done" E5 = "Done" and I want to know how many cells in this range are earlier than or equal to 5/25/08 but later than or equal to 5/12/08 and only count the cells that have "Done" in column E? Should be 2 but how would you constuct a formula for that? Thanks a lot for your help. Dan > Fellas thanks a lot that will help me out! > [quoted text clipped - 43 lines] > > >> > > >> These are nuances of Excel that you learn and get used to over time! Try this: G1 = 5/12/2008 H1 = 5/25/2008 I1 = Done =SUMPRODUCT(--(A1:A5>=G1),--(A1:A5<=H1),--(E1:E5=I1)) SignatureBiff Microsoft Excel MVP > Ok I have another question with this same thing here . . . > Say the dates in A1 thru A5 remain the same. I add the following column [quoted text clipped - 68 lines] >> > >> >> > >> These are nuances of Excel that you learn and get used to over time! Alright that works perfect! Thanks again! > Try this: > [quoted text clipped - 76 lines] > >> > >> > >> > >> These are nuances of Excel that you learn and get used to over time! You're welcome! SignatureBiff Microsoft Excel MVP > Alright that works perfect! > Thanks again! [quoted text clipped - 88 lines] >> >> > >> These are nuances of Excel that you learn and get used to over >> >> > >> time! |
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.