Help with IF AND formula

[john.bedford3]

I have the following formula to count the number of instances of the month
January in a column of dates.

{=SUM(IF(ISNUMBER(C$3:C$512),IF(MONTH(C$3:C$512)=1,1,0)))}

I want to add an extra condition where the year is less than 2002 and have
tried the following adding an "AND" condition but this does not seem to
work.

{=SUM(IF(ISNUMBER(C$3:C$512),IF(AND(YEAR(C$3:C$512)<2002,MONTH(C$3:C$512)=1)
,1,0)))}

I have obviously got the syntax wrong but can not see what my error is. Can
anyone please help?

 

[Bob Phillips]

John,

Try

=SUMPRODUCT(--(ISNUMBER(C$3:C$51)),--(YEAR(C$3:C$51)<2002),--(MONTH(C$3:C$51
)=1))

or even

 

[Duke Carey]

Does this array entered formula do it?

=SUMPRODUCT(--ISNUMBER(C$3:C$512),--(MONTH(C$3:C$512)=1),--(YEAR(C$3:C$512)<2002)

 

[Bob Phillips]

Missing ) at the end of the formula, and it is NOT array entered.

 

[john.bedford3]

Thanks Duke, it does do it and without being entered as an array formula. It
is however, the same as Bob's formula. You must have decided to post at
about the same time.

Thanks for taking the time. Most appreciated,

 

[john.bedford3]

Thanks Bob, that works great.

 

[Guest]

...NOT array entered -

I'm not going to dispute your assertion, Bob, since you and the OP both say
it works, but I *swear* it didn't work for me until array-entering it. That
was after the first cup of coffee for the day, tho <g>

 

[Bob Phillips]

It doesn't need to be array entered Duke, as SUMPRODUCT works on arrays (as
does SUM etc.). SP only gets array entered when you add other non-array
functions.

Regards

Bob