 |
 | 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 / New Users / May 2008How do I count the number of records within a date range
How do I count the number of records within a date range. eg 1/1/2003,1/5/2003,1/10/2004 Using countif between 1/1/2003 and 31/12/2003 = 2 =COUNTIF(A1:A100,">="&"1/1/2003")-COUNTIF(A1:A100,">"&"12/31/2003") Format cell as General > How do I count the number of records within a date range. > eg 1/1/2003,1/5/2003,1/10/2004 Using countif between 1/1/2003 and 31/12/2003 > = 2 "Teethless mama" <Teethlessmama@discussions.microsoft.com> wrote in message > =COUNTIF(A1:A100,">="&"1/1/2003")-COUNTIF(A1:A100,">"&"12/31/2003") This may cause problems in locales that use non-USA date styles. Better to use the DATE function to create the dates. =COUNTIF(A1:A20,"<"&DATE(2006,12,31))-COUNTIF(A1:A20,"<"&DATE(2006,1,1))  SignatureCordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) > =COUNTIF(A1:A100,">="&"1/1/2003")-COUNTIF(A1:A100,">"&"12/31/2003") > [quoted text clipped - 4 lines] >> 31/12/2003 >> = 2 This worked a treat as I am not using USA style - thank you - Mike > "Teethless mama" <Teethlessmama@discussions.microsoft.com> wrote in message > > =COUNTIF(A1:A100,">="&"1/1/2003")-COUNTIF(A1:A100,">"&"12/31/2003") [quoted text clipped - 12 lines] > >> 31/12/2003 > >> = 2 In my case i have a range for a given month on a spreadsheet and I want a tally of timely received applications indicated on a summary spreadsheet, the application is timely if the date received is between the 1st and the 15th of the given month, will this formula subbing out the dates each month work? Right now I am waiting until the 16th of each month to enter the number of applications received manually and I want to automate the process. Thank you for your assistance. SignatureJudy Rose Cohen > "Teethless mama" <Teethlessmama@discussions.microsoft.com> wrote in message > > =COUNTIF(A1:A100,">="&"1/1/2003")-COUNTIF(A1:A100,">"&"12/31/2003") [quoted text clipped - 12 lines] > >> 31/12/2003 > >> = 2 Hi Judy That would work fine, substituting your dates in the formula. You could also enter the date for the month that you require in a cell e.g. in C1 enter 01 May 2008, and with your dates in column A of Sheet1, then =SUMPRODUCT((MONTH($A$1:$A$1000)=MONTH($C$1))*(DAY($A$1:$A$1000)<16)*($A$1:$A$1000)<>"")) Change the ranges to suit. You only need to alter the Date in C1 when you want the figures for another Month SignatureRegards Roger Govier > In my case i have a range for a given month on a spreadsheet and I want a > tally of timely received applications indicated on a summary spreadsheet, [quoted text clipped - 25 lines] >> >> 31/12/2003 >> >> = 2 try =sumproduct((year(daterng)=2003)*1) SignatureDon Guillett SalesAid Software dguillett1@austin.rr.com > How do I count the number of records within a date range. > eg 1/1/2003,1/5/2003,1/10/2004 Using countif between 1/1/2003 and > 31/12/2003 > = 2
|
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.