countifs criteria includes date range

J

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
 
L

Luke M

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

JayH

Works perfectly. Thank you so much

Luke M said:
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!*
 
J

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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top