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

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

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

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

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