If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Countif using dates
In column A I have dates in the following format;
01-Apr-05 right through until the end of March 2006 On a different sheet in my workbook I want to summarise the information by counting the number of appointments between certain dates to get monthly totals. I just don't know how to say that I want all dates between 01-Apr-05 until say 30-Apr-05p and so on. Is it possible to do this - the formula I was trying with was; =COUNT(IF('Master Sheet'!$A$2:$A$10000=DATEVALUE("01/04/2005:30/04/2005"),1,0)) But this isn't working! |
#2
|
|||
|
|||
=SUMPRODUCT(--(A2:A1000=DATE(2005,04,01)),--(A2:A1000=DATE(2005,04,20))
If you are just looking for a month, you can use =SUMPRODUCT(--(MONTH(A2:A1000)=4)) or if there can be multiple yers, then =SUMPRODUCT(--(YEAR(A2:A1000)=2005),--(MONTH(A2:A1000)=4)) or =SUMPRODUCT(--(TEXT(A2:A1000,"yyyymm")="200504") -- HTH RP (remove nothere from the email address if mailing direct) "Bugaglugs" wrote in message ... In column A I have dates in the following format; 01-Apr-05 right through until the end of March 2006 On a different sheet in my workbook I want to summarise the information by counting the number of appointments between certain dates to get monthly totals. I just don't know how to say that I want all dates between 01-Apr-05 until say 30-Apr-05p and so on. Is it possible to do this - the formula I was trying with was; =COUNT(IF('Master Sheet'!$A$2:$A$10000=DATEVALUE("01/04/2005:30/04/2005"),1,0)) But this isn't working! |
#3
|
|||
|
|||
Try...
=SUMPRODUCT(--('Master Sheet'!$A$2:$A$10000=DATE(2005,4,1)),--('Master Sheet'!$A$2:$A$10000=DATE(2005,4,30))) or =SUMPRODUCT(--('Master Sheet'!$A$2:$A$10000-DAY('Master Sheet'!$A$2:$A$10000)+1)=DATE(2005,4,1)) Hope this helps! In article , Bugaglugs wrote: In column A I have dates in the following format; 01-Apr-05 right through until the end of March 2006 On a different sheet in my workbook I want to summarise the information by counting the number of appointments between certain dates to get monthly totals. I just don't know how to say that I want all dates between 01-Apr-05 until say 30-Apr-05p and so on. Is it possible to do this - the formula I was trying with was; =COUNT(IF('Master Sheet'!$A$2:$A$10000=DATEVALUE("01/04/2005:30/04/2005"),1,0)) But this isn't working! |
#4
|
|||
|
|||
This also works:
=SUMPRODUCT(--(A2:A1000="4/1/05"),--(A2:A1000="4/20/05")) "Bob Phillips" wrote in message ... =SUMPRODUCT(--(A2:A1000=DATE(2005,04,01)),--(A2:A1000=DATE(2005,04,20)) If you are just looking for a month, you can use =SUMPRODUCT(--(MONTH(A2:A1000)=4)) or if there can be multiple yers, then =SUMPRODUCT(--(YEAR(A2:A1000)=2005),--(MONTH(A2:A1000)=4)) or =SUMPRODUCT(--(TEXT(A2:A1000,"yyyymm")="200504") -- HTH RP (remove nothere from the email address if mailing direct) "Bugaglugs" wrote in message ... In column A I have dates in the following format; 01-Apr-05 right through until the end of March 2006 On a different sheet in my workbook I want to summarise the information by counting the number of appointments between certain dates to get monthly totals. I just don't know how to say that I want all dates between 01-Apr-05 until say 30-Apr-05p and so on. Is it possible to do this - the formula I was trying with was; =COUNT(IF('Master Sheet'!$A$2:$A$10000=DATEVALUE("01/04/2005:30/04/2005"),1,0)) But this isn't working! |
#5
|
|||
|
|||
Maybe, but we don't format dates like that in the UK, far better to use an
unambiguous way IMO, such as I showed or even =SUMPRODUCT(--(A2:A1000=--"2005-04-01")),--(A2:A1000=--"2005-04-30")) -- HTH RP (remove nothere from the email address if mailing direct) "Bob Umlas" wrote in message ... This also works: =SUMPRODUCT(--(A2:A1000="4/1/05"),--(A2:A1000="4/20/05")) "Bob Phillips" wrote in message ... =SUMPRODUCT(--(A2:A1000=DATE(2005,04,01)),--(A2:A1000=DATE(2005,04,20)) If you are just looking for a month, you can use =SUMPRODUCT(--(MONTH(A2:A1000)=4)) or if there can be multiple yers, then =SUMPRODUCT(--(YEAR(A2:A1000)=2005),--(MONTH(A2:A1000)=4)) or =SUMPRODUCT(--(TEXT(A2:A1000,"yyyymm")="200504") -- HTH RP (remove nothere from the email address if mailing direct) "Bugaglugs" wrote in message ... In column A I have dates in the following format; 01-Apr-05 right through until the end of March 2006 On a different sheet in my workbook I want to summarise the information by counting the number of appointments between certain dates to get monthly totals. I just don't know how to say that I want all dates between 01-Apr-05 until say 30-Apr-05p and so on. Is it possible to do this - the formula I was trying with was; =COUNT(IF('Master Sheet'!$A$2:$A$10000=DATEVALUE("01/04/2005:30/04/2005"),1,0)) But this isn't working! |
#6
|
|||
|
|||
Bob - thanks for you prompt response - I'm still having problems, do you
think it's how I've formatted the cells with the dates in? I've got them so that however the user inputs the date it comes up "01-Apr-05" format? "Bob Phillips" wrote: =SUMPRODUCT(--(A2:A1000=DATE(2005,04,01)),--(A2:A1000=DATE(2005,04,20)) If you are just looking for a month, you can use =SUMPRODUCT(--(MONTH(A2:A1000)=4)) or if there can be multiple yers, then =SUMPRODUCT(--(YEAR(A2:A1000)=2005),--(MONTH(A2:A1000)=4)) or =SUMPRODUCT(--(TEXT(A2:A1000,"yyyymm")="200504") -- HTH RP (remove nothere from the email address if mailing direct) "Bugaglugs" wrote in message ... In column A I have dates in the following format; 01-Apr-05 right through until the end of March 2006 On a different sheet in my workbook I want to summarise the information by counting the number of appointments between certain dates to get monthly totals. I just don't know how to say that I want all dates between 01-Apr-05 until say 30-Apr-05p and so on. Is it possible to do this - the formula I was trying with was; =COUNT(IF('Master Sheet'!$A$2:$A$10000=DATEVALUE("01/04/2005:30/04/2005"),1,0)) But this isn't working! |
#7
|
|||
|
|||
If they are dates, the format should not matter at all.
What problems are you experiencing? Do you get 0 or an answer which you can't see the reason for? -- HTH RP (remove nothere from the email address if mailing direct) "Bugaglugs" wrote in message ... Bob - thanks for you prompt response - I'm still having problems, do you think it's how I've formatted the cells with the dates in? I've got them so that however the user inputs the date it comes up "01-Apr-05" format? "Bob Phillips" wrote: =SUMPRODUCT(--(A2:A1000=DATE(2005,04,01)),--(A2:A1000=DATE(2005,04,20)) If you are just looking for a month, you can use =SUMPRODUCT(--(MONTH(A2:A1000)=4)) or if there can be multiple yers, then =SUMPRODUCT(--(YEAR(A2:A1000)=2005),--(MONTH(A2:A1000)=4)) or =SUMPRODUCT(--(TEXT(A2:A1000,"yyyymm")="200504") -- HTH RP (remove nothere from the email address if mailing direct) "Bugaglugs" wrote in message ... In column A I have dates in the following format; 01-Apr-05 right through until the end of March 2006 On a different sheet in my workbook I want to summarise the information by counting the number of appointments between certain dates to get monthly totals. I just don't know how to say that I want all dates between 01-Apr-05 until say 30-Apr-05p and so on. Is it possible to do this - the formula I was trying with was; =COUNT(IF('Master Sheet'!$A$2:$A$10000=DATEVALUE("01/04/2005:30/04/2005"),1,0)) But this isn't working! |
#8
|
|||
|
|||
Bob - spot on, I'm getting a '0' which I can't see the reason for!
"Bob Phillips" wrote: If they are dates, the format should not matter at all. What problems are you experiencing? Do you get 0 or an answer which you can't see the reason for? -- HTH RP (remove nothere from the email address if mailing direct) "Bugaglugs" wrote in message ... Bob - thanks for you prompt response - I'm still having problems, do you think it's how I've formatted the cells with the dates in? I've got them so that however the user inputs the date it comes up "01-Apr-05" format? "Bob Phillips" wrote: =SUMPRODUCT(--(A2:A1000=DATE(2005,04,01)),--(A2:A1000=DATE(2005,04,20)) If you are just looking for a month, you can use =SUMPRODUCT(--(MONTH(A2:A1000)=4)) or if there can be multiple yers, then =SUMPRODUCT(--(YEAR(A2:A1000)=2005),--(MONTH(A2:A1000)=4)) or =SUMPRODUCT(--(TEXT(A2:A1000,"yyyymm")="200504") -- HTH RP (remove nothere from the email address if mailing direct) "Bugaglugs" wrote in message ... In column A I have dates in the following format; 01-Apr-05 right through until the end of March 2006 On a different sheet in my workbook I want to summarise the information by counting the number of appointments between certain dates to get monthly totals. I just don't know how to say that I want all dates between 01-Apr-05 until say 30-Apr-05p and so on. Is it possible to do this - the formula I was trying with was; =COUNT(IF('Master Sheet'!$A$2:$A$10000=DATEVALUE("01/04/2005:30/04/2005"),1,0)) But this isn't working! |
#9
|
|||
|
|||
Domenic
Thanks - still puzzling over this as I copied and pasted the formula and am now getting #ref I'm sure this is me rather than you but I just can't understand it! "Domenic" wrote: Try... =SUMPRODUCT(--('Master Sheet'!$A$2:$A$10000=DATE(2005,4,1)),--('Master Sheet'!$A$2:$A$10000=DATE(2005,4,30))) or =SUMPRODUCT(--('Master Sheet'!$A$2:$A$10000-DAY('Master Sheet'!$A$2:$A$10000)+1)=DATE(2005,4,1)) Hope this helps! In article , Bugaglugs wrote: In column A I have dates in the following format; 01-Apr-05 right through until the end of March 2006 On a different sheet in my workbook I want to summarise the information by counting the number of appointments between certain dates to get monthly totals. I just don't know how to say that I want all dates between 01-Apr-05 until say 30-Apr-05p and so on. Is it possible to do this - the formula I was trying with was; =COUNT(IF('Master Sheet'!$A$2:$A$10000=DATEVALUE("01/04/2005:30/04/2005"),1,0)) But this isn't working! |
#10
|
|||
|
|||
Thank you for all your help - have now found out the reason for my problems
when using all the suggested formulas....am VERY sorry to admit that I'd put in the wrong date when trying it out so it was a simple case of user error on my part. Thanks once again for all time taken from everyone! "Bugaglugs" wrote: Domenic Thanks - still puzzling over this as I copied and pasted the formula and am now getting #ref I'm sure this is me rather than you but I just can't understand it! "Domenic" wrote: Try... =SUMPRODUCT(--('Master Sheet'!$A$2:$A$10000=DATE(2005,4,1)),--('Master Sheet'!$A$2:$A$10000=DATE(2005,4,30))) or =SUMPRODUCT(--('Master Sheet'!$A$2:$A$10000-DAY('Master Sheet'!$A$2:$A$10000)+1)=DATE(2005,4,1)) Hope this helps! In article , Bugaglugs wrote: In column A I have dates in the following format; 01-Apr-05 right through until the end of March 2006 On a different sheet in my workbook I want to summarise the information by counting the number of appointments between certain dates to get monthly totals. I just don't know how to say that I want all dates between 01-Apr-05 until say 30-Apr-05p and so on. Is it possible to do this - the formula I was trying with was; =COUNT(IF('Master Sheet'!$A$2:$A$10000=DATEVALUE("01/04/2005:30/04/2005"),1,0)) But this isn't working! |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
COUNTIF With Multiple Dates, Columns and Text | Shannon | Worksheet Functions | 4 | November 26th, 2004 11:12 PM |
Countif a range of dates | Scott | Worksheet Functions | 3 | June 18th, 2004 01:18 AM |
CountIf function + dates | patricia | Worksheet Functions | 3 | May 24th, 2004 11:27 PM |
Can "countif" formula be nested using dates | Bob Phillips | Worksheet Functions | 0 | April 6th, 2004 09:11 PM |
Countif value between two dates | Gary | Worksheet Functions | 6 | November 14th, 2003 01:09 AM |