Help with IF AND formula

  • Thread starter Thread starter john.bedford3
  • Start date Start date
J

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?
 
John,

Try

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

or even
 
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)
 
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,
 
...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>
 
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
 
Back
Top