KL.
Many thanks - easy when you know how !!
rgds
Anthony

"KL" wrote:

Hi Anthony,

Assuming that the data in range [B3:B100] are real dates and not text (if
you can change their look by formatting then it is probable the former), you
could do one of the following (keeping in mind that formatting itself
doesn't change the cell value, so no mattaer what format you apply to a cell
that initially showed 21-July-05 the value of the cell remains 38554):

=SUMPRODUCT((MONTH(B3:B100)=7)*(C3:G100<""))

or (much less efficient)

=SUMPRODUCT((UPPER(TEXT(B3:B100,"mmmm"))="JULY")*( C3:G100<""))

Regards,
KL


"Anthony" wrote in message
...
Hi,
In a workbook Cell G23 is a data box with a dd-mmmm-yy format.
On execution of a macro this data (and lots more) is copy/pasted to
another
worksheet. Cell G23 is copy/pasted to next avaiable row in column B.
I need to be able to count the amont of times each different month is
shown
in column B, but the pasted data is in the formatt dd-mmmm-yy and my count
formula is just looking for the 'month' name.
I have tried formatting the whole of column B to become just mmmm but this
doesn't work.
Here is the formula that I am using to do the inital 'count'

=SUMPRODUCT((B3:B100="JULY")*(C3:G100<""))

EG
when I paste the date given by the user as 21July05 into the cell required
I
need this cell to show JUST 'July' so that it will be counted!

Any ideas/help ??
many thanks