 |
 | 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 / July 2007Using countif with a range of dates
Column Z contains multiple dates ranging over 10 years. I am trying to count the number of cells in Column A that are between two dates (e.g., 01/01/07 - 01/31/07.) I tried using =SUM((COUNTIF(Sheet2!Z:Z,"<01/31/07"))-(COUNTIF(Sheet2!Z:Z,">01/01/07"))) however, the count if not correct. The result is 3,172 via the above formula which is wrong. Using a filter, I know the correct result is 56, but I cannot correct my formula to reflect. Many Thanks! =COUNTIF(Sheet2!Z:Z,"<="&01/31/07)-COUNTIF(Sheet2!Z:Z,"<"&01/01/07)  SignatureWigi http://www.wimgielis.be = Excel/VBA, soccer and music > Column Z contains multiple dates ranging over 10 years. I am trying to count > the number of cells in Column A that are between two dates (e.g., 01/01/07 - [quoted text clipped - 8 lines] > > Many Thanks! Thanks, but that didn't work. The result was 0. I'm curious about the use of the "&" as well as using the < > inbetween the " marks. > =COUNTIF(Sheet2!Z:Z,"<="&01/31/07)-COUNTIF(Sheet2!Z:Z,"<"&01/01/07) > [quoted text clipped - 11 lines] > > > > Many Thanks! Try this: =COUNTIF(Z:Z,">=1/1/07")-COUNTIF(Z:Z,">1/31/07") SignatureHTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- > Thanks, but that didn't work. The result was 0. I'm curious about the > use [quoted text clipped - 17 lines] >> > >> > Many Thanks! Thank you! > Try this: > [quoted text clipped - 21 lines] > >> > > >> > Many Thanks! =COUNTIF(Sheet2!Z:Z,"<="&DATE(2007,1,31))-COUNTIF(Sheet2!Z:Z,"<"&DATE(2007,1,1)) You could also use this kind of formula (but not the whole column until xl2007): =sumproduct(--(text(sheet2!z1:z999,"yyyymm")="200701")) > Column Z contains multiple dates ranging over 10 years. I am trying to count > the number of cells in Column A that are between two dates (e.g., 01/01/07 - [quoted text clipped - 8 lines] > > Many Thanks! SignatureDave Peterson |
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.