| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countifs criteria includes date range
After some help please - at my wits end now! I am using Excel 2007 and am
trying to count cells which meet a certain criteria. Originally had no problems with this. Colum G contains 'subjects' and E contains a date entry formatted dd.mm.yy =COUNTIFS('On Call'!G:G,"Math*",'On Call'!E:E,"*.09.09") Had trouble sorting in date order with this format as it is not a 'date' as such. The date is now being entered dd/mm/yyyy and is formatted as a date but alas my formula now returns zeros throughout =COUNTIFS('On Call'!G:G,"Math*",'On Call'!E:E,"*/09/2009") I've tried removing the the wildcards and replacing with whole date but that makes no difference. i've tried numerous different counts, sumifs, sumproduct to no avail. this one does produce a result but not the correct one! =COUNTIFS('On Call'!$G$2:$G$220,"=English",'On Call'!$E$2:$E$220,"<="&DATE(2009,11,31)) Any help would be gratefully received |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
countifs criteria includes date range
Perhaps this?
=SUMPRODUCT(--(LEFT('On Call'!G:G,4)="Math"),--(TEXT('On Call'!E:E,"mmyyyy")="092009")) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "JayH" wrote: After some help please - at my wits end now! I am using Excel 2007 and am trying to count cells which meet a certain criteria. Originally had no problems with this. Colum G contains 'subjects' and E contains a date entry formatted dd.mm.yy =COUNTIFS('On Call'!G:G,"Math*",'On Call'!E:E,"*.09.09") Had trouble sorting in date order with this format as it is not a 'date' as such. The date is now being entered dd/mm/yyyy and is formatted as a date but alas my formula now returns zeros throughout =COUNTIFS('On Call'!G:G,"Math*",'On Call'!E:E,"*/09/2009") I've tried removing the the wildcards and replacing with whole date but that makes no difference. i've tried numerous different counts, sumifs, sumproduct to no avail. this one does produce a result but not the correct one! =COUNTIFS('On Call'!$G$2:$G$220,"=English",'On Call'!$E$2:$E$220,"<="&DATE(2009,11,31)) Any help would be gratefully received |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
countifs criteria includes date range
Works perfectly. Thank you so much
"Luke M" wrote: Perhaps this? =SUMPRODUCT(--(LEFT('On Call'!G:G,4)="Math"),--(TEXT('On Call'!E:E,"mmyyyy")="092009")) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "JayH" wrote: After some help please - at my wits end now! I am using Excel 2007 and am trying to count cells which meet a certain criteria. Originally had no problems with this. Colum G contains 'subjects' and E contains a date entry formatted dd.mm.yy =COUNTIFS('On Call'!G:G,"Math*",'On Call'!E:E,"*.09.09") Had trouble sorting in date order with this format as it is not a 'date' as such. The date is now being entered dd/mm/yyyy and is formatted as a date but alas my formula now returns zeros throughout =COUNTIFS('On Call'!G:G,"Math*",'On Call'!E:E,"*/09/2009") I've tried removing the the wildcards and replacing with whole date but that makes no difference. i've tried numerous different counts, sumifs, sumproduct to no avail. this one does produce a result but not the correct one! =COUNTIFS('On Call'!$G$2:$G$220,"=English",'On Call'!$E$2:$E$220,"<="&DATE(2009,11,31)) Any help would be gratefully received |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
countifs criteria includes date range
Additinal problem - i have just updated all my cells with this formula for
different subjects and months but now every time a make an amendment in the 'On Call' sheet it takes ages; click on a cell and wait 5 minutes; amend cell and wait 5 minutes (and i think 5 minutes is a conservative estimate!) It says in the bottom right corner "Calculating: 2 Processor(s)): n%". Any ideas? "Luke M" wrote: Perhaps this? =SUMPRODUCT(--(LEFT('On Call'!G:G,4)="Math"),--(TEXT('On Call'!E:E,"mmyyyy")="092009")) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "JayH" wrote: After some help please - at my wits end now! I am using Excel 2007 and am trying to count cells which meet a certain criteria. Originally had no problems with this. Colum G contains 'subjects' and E contains a date entry formatted dd.mm.yy =COUNTIFS('On Call'!G:G,"Math*",'On Call'!E:E,"*.09.09") Had trouble sorting in date order with this format as it is not a 'date' as such. The date is now being entered dd/mm/yyyy and is formatted as a date but alas my formula now returns zeros throughout =COUNTIFS('On Call'!G:G,"Math*",'On Call'!E:E,"*/09/2009") I've tried removing the the wildcards and replacing with whole date but that makes no difference. i've tried numerous different counts, sumifs, sumproduct to no avail. this one does produce a result but not the correct one! =COUNTIFS('On Call'!$G$2:$G$220,"=English",'On Call'!$E$2:$E$220,"<="&DATE(2009,11,31)) Any help would be gratefully received |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
countifs criteria includes date range
Hi,
Instead of giving the whole column as range, define an explicit range such as G4:G5000, E5:E5000 -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "JayH" wrote in message ... Additinal problem - i have just updated all my cells with this formula for different subjects and months but now every time a make an amendment in the 'On Call' sheet it takes ages; click on a cell and wait 5 minutes; amend cell and wait 5 minutes (and i think 5 minutes is a conservative estimate!) It says in the bottom right corner "Calculating: 2 Processor(s)): n%". Any ideas? "Luke M" wrote: Perhaps this? =SUMPRODUCT(--(LEFT('On Call'!G:G,4)="Math"),--(TEXT('On Call'!E:E,"mmyyyy")="092009")) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "JayH" wrote: After some help please - at my wits end now! I am using Excel 2007 and am trying to count cells which meet a certain criteria. Originally had no problems with this. Colum G contains 'subjects' and E contains a date entry formatted dd.mm.yy =COUNTIFS('On Call'!G:G,"Math*",'On Call'!E:E,"*.09.09") Had trouble sorting in date order with this format as it is not a 'date' as such. The date is now being entered dd/mm/yyyy and is formatted as a date but alas my formula now returns zeros throughout =COUNTIFS('On Call'!G:G,"Math*",'On Call'!E:E,"*/09/2009") I've tried removing the the wildcards and replacing with whole date but that makes no difference. i've tried numerous different counts, sumifs, sumproduct to no avail. this one does produce a result but not the correct one! =COUNTIFS('On Call'!$G$2:$G$220,"=English",'On Call'!$E$2:$E$220,"<="&DATE(2009,11,31)) Any help would be gratefully received |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| COUNTIFS AND CRITERIA | Excel Worksheet Functions | |||
| Selecting data in a specific date range using COUNTIFS function | Excel Worksheet Functions | |||
| countifs month criteria | Excel Discussion (Misc queries) | |||
| COUNTIFs with multiple criteria | Excel Discussion (Misc queries) | |||
| =max(range includes #N/As) | Excel Worksheet Functions | |||
Hybrid Mode
