 |
 | 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 2008Count on dates
Hi all, Thanks for the previous help. Now i am struck with another problem hope you guys can help me out. My issue is i have two columns like Col1 Col2 Date date from to 15/04/2008 14/03/2009 .... and so on (Sixty thousand records with different dates) now i want to filter the data on Month wise for instance... i want to count all the values under the month Apr-09, May-09, June-09 etc. Hope i am clear in asking the question. A speedy help will be greatly appreciated. =SUMPRODUCT(--(MONTH(B2:B6000)>3),--(MONTH(B2:B6000)<7),--(YEAR(B2:B6000)=2009)) > Hi all, > Thanks for the previous help. [quoted text clipped - 11 lines] > Hope i am clear in asking the question. > A speedy help will be greatly appreciated. Dear friend the result of your advice is as follows 15/04/2008 14/03/2009 0 16/04/2008 15/03/2009 0 17/04/2008 16/03/2009 0 18/04/2008 17/03/2009 0 19/04/2008 18/03/2009 0 20/04/2008 19/03/2009 0 21/04/2008 20/03/2009 0 22/04/2008 21/03/2009 0 23/04/2008 22/03/2009 0 24/04/2008 23/03/2009 0 25/04/2008 24/03/2009 0 26/04/2008 25/03/2009 0 27/04/2008 26/03/2009 0 28/04/2008 27/03/2009 0 29/04/2008 28/03/2009 0 30/04/2008 29/03/2009 0 Please advice.... > =SUMPRODUCT(--(MONTH(B2:B6000)>3),--(MONTH(B2:B6000)<7),--(YEAR(B2:B6000)=2009)) > [quoted text clipped - 13 lines] > > Hope i am clear in asking the question. > > A speedy help will be greatly appreciated. Hi, Like Ashish has already pointed out there isn't enough information to answer this:- 15/04/2008 14/03/2009 0 16/04/2008 15/03/2009 0 17/04/2008 16/03/2009 0 18/04/2008 17/03/2009 0 19/04/2008 18/03/2009 0 > > > i want to count all the values under the month Apr-09, May-09, June-09 etc. From the data posted above which dates are in April. Each pair of dates covers and entire year!! What values are we counting? Mike > Dear friend the result of your advice is as follows > [quoted text clipped - 34 lines] > > > Hope i am clear in asking the question. > > > A speedy help will be greatly appreciated. Hi, On what column would you like to check for Apr-09, Jun-09 etc. Please clarify.  SignatureRegards, Ashsih Mathur Microsoft Excel MVP www.ashishmathur.com > Hi all, > Thanks for the previous help. [quoted text clipped - 12 lines] > Hope i am clear in asking the question. > A speedy help will be greatly appreciated. Dear ashish i want to check up on "To" column ( ie., column 2) > Hi, > [quoted text clipped - 17 lines] > > Hope i am clear in asking the question. > > A speedy help will be greatly appreciated. Dear friends, thanks for your speedy help and Mike my apologies if i didnot ask the question properly. I want to find the number of cells in column2 basing on the month April. example i found 16 cells in the example which i pasted in this post. Like wise on every month. hope i am clear in asking this time. Thanks once again > Dear ashish > i want to check up on "To" column ( ie., column 2) [quoted text clipped - 20 lines] > > > Hope i am clear in asking the question. > > > A speedy help will be greatly appreciated. Maybe this =SUMPRODUCT(--(MONTH(A1:A16)=4)) Mike > Dear friends, > thanks for your speedy help and Mike my apologies if i didnot ask the [quoted text clipped - 30 lines] > > > > Hope i am clear in asking the question. > > > > A speedy help will be greatly appreciated. Give this a try for April 2009... =SUMPRODUCT((MONTH(B1:B70000)=4)*(YEAR(B1:B70000)=2009)) Change the 4 (month = April) and 2009 (year of interest) to suit your conditions. I used 70000 rows to make sure I covered the 60000 rows (records) you mentioned in your first posting... the number used (same in both ranges) has to be larger than the number of actual records being examined. SignatureRick (MVP - Excel) > Dear friends, > thanks for your speedy help and Mike my apologies if i didnot ask the [quoted text clipped - 31 lines] >> > > Hope i am clear in asking the question. >> > > A speedy help will be greatly appreciated. Rick, When i tried that i am getting a "#value" error Help me out > Give this a try for April 2009... > [quoted text clipped - 41 lines] > >> > > Hope i am clear in asking the question. > >> > > A speedy help will be greatly appreciated. I tested the formula before I posted it and it worked for me at that time. Do you have *real* dates in Column B or are they text values (you can check by looking at Format/Cells)? SignatureRick (MVP - Excel) > Rick, > When i tried that i am getting a "#value" error Help me out [quoted text clipped - 48 lines] >> >> > > Hope i am clear in asking the question. >> >> > > A speedy help will be greatly appreciated. Rick My dates are *real* dates and for your information my date settings are in dd/mm/yyyy. Please look into this and advice. Thanks once again > I tested the formula before I posted it and it worked for me at that time. > Do you have *real* dates in Column B or are they text values (you can check [quoted text clipped - 52 lines] > >> >> > > Hope i am clear in asking the question. > >> >> > > A speedy help will be greatly appreciated. When Rick said that you could check by using Format/Cells, what I think he meant is that if you use Format/ Cells to change the date format temporarily, for example to dd mmm yyyy, if the display in the cell changes they are real dates, but if they don't change they are text. Try that. Also, remember to check all the rows in the column. If, for example, you have a field description saying "date" in cell B1, this would give a #VALUE! error from the formula. You could check with =SUM(--(ISTEXT(B1:B70000))) as an *array formula* entered with Control Shift Enter. It should give zero if you have no text entries in your range. -- David Biddulph > Rick > My dates are *real* dates and for your information my date settings are in [quoted text clipped - 62 lines] >> >> >> > > Hope i am clear in asking the question. >> >> >> > > A speedy help will be greatly appreciated. Friends, Thanks once again for the help. Rick, once again thanks, the formula advised by you a master piece. Only mistake i did was there are some gaps in the column which were filled by "-", so the formula doesn't worked out. Any how thanks once again you people. > When Rick said that you could check by using Format/Cells, what I think he > meant is that if you use Format/ Cells to change the date format [quoted text clipped - 76 lines] > >> >> >> > > Hope i am clear in asking the question. > >> >> >> > > A speedy help will be greatly appreciated. So you don't have an answer to your question yet then, right? Give this array-entered** formula a try... =SUM(IF(ISNUMBER(B1:B65535),MONTH(B1:B65535)=11,0)*IF(ISNUMBER(B1:B65535),YEAR(B1:B65535)=2008,0)) **Commit the formula with Ctrl+Shift+Enter, not just with Enter by itself. SignatureRick (MVP - Excel) > Friends, > Thanks once again for the help. Rick, once again thanks, the formula [quoted text clipped - 95 lines] >> >> >> >> > > Hope i am clear in asking the question. >> >> >> >> > > A speedy help will be greatly appreciated. Off course i got the answer for the problem by replacing the "-" with space any how once again thanks for taking much pain in this. And another request posted by me was at "http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft. public.excel.worksheet.functions&tid=90b0d103-f35c-4a98-b56d-3a4525f50d60&cat=&l ang=en&cr=US&sloc=&m=1&p=1" can u just look into this and suggest me an idea Thanks once again > So you don't have an answer to your question yet then, right? > [quoted text clipped - 103 lines] > >> >> >> >> > > Hope i am clear in asking the question. > >> >> >> >> > > A speedy help will be greatly appreciated. Post an example of what data you have and what you want. SignatureRick (MVP - Excel) > Off course i got the answer for the problem by replacing the "-" with > space [quoted text clipped - 129 lines] >> >> >> >> >> > > Hope i am clear in asking the question. >> >> >> >> >> > > A speedy help will be greatly appreciated. |
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.