countifs criteria includes date range

[JayH]

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

 

[Luke M]

Perhaps this?
=SUMPRODUCT(--(LEFT('On Call'!G:G,4)="Math"),--(TEXT('On
Call'!E:E,"mmyyyy")="092009"))

 

[JayH]

Works perfectly. Thank you so much

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]

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?

 

[Ashish Mathur]

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