COUNTIF, cells including certain text

[matt_the_brum]

Easy problem but I'm stuck.
I want to use a formula to count the number of cells from a column o
another sheet which includeany date in January. I have trie
=COUNTIF(Enquiries!D,"Jan-04") but it is only counting exact matche
and not cells that inclde this

 

[JMay]

Two things, in your formula you have said you are looking for "Jan-04",
making it TEXT;
This will only work if the source is TEXT.
If your source data is numeric (excel date) you can extract the month by
using
=month(A1) where A1 has a date 02/04/04 (the number 38021)
HTH

 

[matt_the_brum]

I have changed all my dates to numeric ones ie 03/01/04 and trie
variations along the lines of this
=COUNTIF(Enquiries!D,"=MONTH(A2)")
But still no joy

 

[Frank Kabel]

Hi
try the following (if column D is a real date)
=SUMPRODUCT(('Enquiries'!D1999>=date(2004,1,1))*('Enquiries'!D1999<
=date(2004,1,31)))
to get all occurences for January 2004
Frank

 

[Bob Phillips]

Matt,

Try this

=SUMPRODUCT((TEXT(D1100,"mmmm")="January")*(NOT(ISBLANK(D1100))))

or this

=SUMPRODUCT((MONTH(D1100)=1)*(NOT(ISBLANK(D1100))))


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)