Compare if same month in cell vs columns

M

MHY

On sheet 1 I have the months across the top row and lets say that the month
of March is in c1. On sheet 2 I have a column (d1:d20) with many dates, and
I want to count the number of dates in the month of March.
I think it would go something like this,
=countif(month(sheet 2(d1:d20)),sheet 1(c1))
I may not have all of the () in the right place but hope you get the idea.
Thansk
 
R

RagDyer

Assume Sheet1 column headers are plain text, while dates in Sheet2 are true
dates, try this *array* formula:

=COUNT(IF(TEXT(Sheet2!D1:D20,"mmmm")=C1,Sheet2!D1:D20))

--
Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the
regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================
 
M

MHY

Sorry, I should have stated that both can be in the same format. mmm-yy,
mm-dd-yr. Thanks
 
R

RagDyer

Then try this *array* formula:

=COUNT(IF(MONTH(Sheet2!D1:D20)=MONTH(C1),Sheet2!D1:D20))


--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================
 
M

MHY

I would like to show you what I mean, so let me try again. On one
worksheet(sheet #1) i have the months across the top, Jan thru Dec. (Jan 05
Feb 05 Mar 05.....Dec 05)
On a second worksheet (sheet #2) I have a column of random dates of 05.
Assume that in the column on sheet #2 there are 15 dates in May 05. I want
to be able to count the dates in May and show them on Sheet#1 under May 05).
Below you suggested
=COUNT(IF(MONTH(Sheet2!D1:D20)=MONTH(C1),Sheet2!D1:D20)) and it seems to
return alot of errors.
 
R

RagDyer

First of all, this is an *array* formula, so, to repeat:

Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the
regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.

Did you enter the formula correctly?

Second, are the dates on both sheets *true* XL recognized dates, and *not*
text entries that you keyed in?
 
Top