 |
 | 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 / November 2006Counting data for a particular month
Hi there! I have two columns of data, first one would be my contract number and second one would be the date of completion of the contract. How do I go about using summing up the number of contracts I've completed in a particular month? For example: Contract Number Date ------------------------ -------- 27 1/11/2006 87 2/8/2006 76 1/1/2006 45 4/8/2006 78 9/12/2006 41 2/1/2006 08 9/6/2006 . . So from the above, I need the number of contracts I've done for the month of January, February, so on and so forth. I'm kind of stuck with dates for EXCEL. Yup that's the general idea. Thanks! Regards Maccann Yeo One way to count the number of contracts done in January of 2006: =sumproduct(--(text(b1:b100,"yyyymm")="200601") Adjust the ranges to match--but you can't use whole columns. =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail here: http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html But if you have lots of months to check, you may want to look into data|Pivottable. You can group the dates by month and year and get a count pretty easily. > Hi there! > [quoted text clipped - 22 lines] > Regards > Maccann Yeo  SignatureDave Peterson Hi. So sorry, but I think I conveyed the wrong idea due to the wrong wording used. My purpose is to actually count the number of contracts that were completed in the month of January, February and so on, not really the sum. For instance, I'd want to know that the number of contracts that took place in January was 2 (The format is in the ddmmyyyy format), February: 0, June: 1, August: 2 and so on Sorry for the confusion! > One way to count the number of contracts done in January of 2006: > [quoted text clipped - 41 lines] > > Regards > > Maccann Yeo Hi Dave had a slight typo in his formula. He meant to type =SUMPRODUCT(--(TEXT(B1:B100,"yyyymm")="200601")) It will give you a count of the number of contracts completed in the month of January 2006 Don't take any notice of the fact that it is Sumproduct being used, it will in this instance produce a count, not a Sum. Each test will return True or False. The double unary minus -- coerces True's to 1's and False's to 0's, which Sumproduct then sums to give you the total number of matches. Alternatively you could use =SUMPRODUCT(--(MONTH(B1:B100)=1)) for January, 2 for February etc. but be aware that if you had more than one year's worth of data, it would add January 06 and January 07 results together. Dave's method takes account of Year as well as month. SignatureRegards Roger Govier > Hi. So sorry, but I think I conveyed the wrong idea due to the wrong > wording used. [quoted text clipped - 59 lines] >> > Regards >> > Maccann Yeo Thanks for the correction, Roger (but excel would have corrected it, too <bg>.) And just another warning... =SUMPRODUCT(--(MONTH(B1:B100)=1)) will count empty cells as January, too. I'd add a check: =SUMPRODUCT(--(MONTH(B1:B100)=1),--ISNUMBER(B1:B100)) > Hi > Dave had a slight typo in his formula. He meant to type [quoted text clipped - 86 lines] > >> > >> Dave Peterson SignatureDave Peterson Absolutely right Dave, as always <vbg> SignatureRegards Roger Govier > Thanks for the correction, Roger (but excel would have corrected it, > too <bg>.) [quoted text clipped - 110 lines] >> >> >> >> Dave Peterson You have to read more of my posts and you'll be busier with corrections! <bg> > Absolutely right Dave, as always <vbg> > [quoted text clipped - 121 lines] > > > > Dave Peterson SignatureDave Peterson Hey guys, I've managed to get it to work. Thank you so much! Regards Maccann > You have to read more of my posts and you'll be busier with corrections! <bg> > [quoted text clipped - 123 lines] > > > > > > Dave 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.