 |
 | 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 / January 2007Count Days?
Is there a way to count the specific instances/occurances of dates? I may run as many as 3 delivery trucks on a specific day. On some days no trucks run. I want to count how many days in a month I deliver. Without counting the multiples. I have a list of dates in column A . Column a represents data about each truck run. The other columns show other delivery related data. 01/02/06 01/02/06 01/03/06 01/05/06 02/05/06 02/15/06 etc Is there a way to count the number of days represented/delivered in January (or any specified month) 2006 WITHOUT counting January 2nd 2X's? Just the number of days out of 31 that have shown activity? Say your dates are in A1 to A100. Enter the month you're looking to count into D1, using the format of "mmm" (Jan, Feb, Mar, ...etc.). Then try this *array* formula: =COUNT(1/FREQUENCY(IF((TEXT(A1:A100,"mmm")=D1)*(A1:A100<>""),MATCH(A1:A100,A 1:A100,0)),ROW(1:100)))  SignatureArray formulas must be entered with CSE, <Ctrl> <Shift > <Enter>, instead of the regular <Enter>, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. You *must also* use CSE when revising the formula. Note: This will count all unique dates for a particular month, no matter what the year.! -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- > Is there a way to count the specific instances/occurances of dates? > [quoted text clipped - 20 lines] > > Just the number of days out of 31 that have shown activity? RD, Thanks for the quick reply, however I could not get it to work. My column A represents each delivery route. I may have 90 delivery route entries in January, but I may only actually be running trucks on 23 days in January. There will be many days that are duplicated because I am running 3-4 trucks on that day, but I want excel to count that day as 1. If I run 1 truck, it should count as 1. If I run multiple trucks, it should count as 1, if I run no trucks, it should count as 0. How do I count just the specific days in January 2006 I was delivering? > Say your dates are in A1 to A100. > [quoted text clipped - 46 lines] > > > > Just the number of days out of 31 that have shown activity? What does "not get it to work" mean? No answer - Wrong answer - Error message? The array formula that I suggested will count unique dates entered in Column A, from A1 to A100, where the date matches the month that is entered into cell D1 in the 3 letter abbreviated format of the example in my post (Jan - Feb). ALSO, the dates must be true XL recognized dates, which also means that there can be *nothing else* in the cell, except the date ... And the formula must be entered using CSE (<Ctrl> <Shift> <Enter>). Be more explicit as to what is "not working"! SignatureRegards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- > RD, > [quoted text clipped - 18 lines] > > > > Then try this *array* formula: =COUNT(1/FREQUENCY(IF((TEXT(A1:A100,"mmm")=D1)*(A1:A100<>""),MATCH(A1:A100,A > > 1:A100,0)),ROW(1:100))) > > [quoted text clipped - 39 lines] > > > > > > Just the number of days out of 31 that have shown activity? Here are the only 3 dates I have entered yet in column A. The formula returns 1. I can send you the small worksheet to look at. I do have the dates in a5:a100 and I changed those in the formula you sent me. THe dates below are in a5:a7 the remainder is blank at this time. I will eventually have up to 3000 date lines. I did array enter it too. 1/31/2006 1/30/2006 12/20/2006 > What does "not get it to work" mean? > [quoted text clipped - 91 lines] > > > > > > > > Just the number of days out of 31 that have shown activity? I put your dates in A1:A3 and JAN in D1 (not a date, just the letters JAN in D1). RD's formula returned 2. It returned 1 if I didn't hit ctrl-shift-enter, though. > Here are the only 3 dates I have entered yet in column A. The formula > returns 1. [quoted text clipped - 105 lines] > > > > > > > > > > Just the number of days out of 31 that have shown activity? SignatureDave Peterson What does RD's formula return for you? Works for me if entered in any cell but D1. Don't forget the CTRL + SHIFT + ENTER Enter Jan in D1 Gord Dibben MS Excel MVP >RD, > [quoted text clipped - 62 lines] >> > >> > Just the number of days out of 31 that have shown activity?
|
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.