Sumproduct date forumla regards 1st of the month as occurring in 2 seperate months

Hello all,

I have the following formula which gives me the sum of accidents in a specific month:

=SUMPRODUCT((Incidents!$A$4:$A$302=$A$1)*(Incidents!$B$4:$B$302=B$2)*(Incidents!$C$4:$C$302>=$A10)*(Incidents!$C$4:$C$302<=DATE(YEAR($A10),MONTH($A10)+1,1)))

A1 is the department
B2 is the incident code (e.g. "1p")
A10 is the month (in this case august)

The problem I have is that if an incident occurs on the 1st of the month my formula counts it as two seperate incidents, in two seperate months

For example, on the dummy workbook provided:

a 1p incident, in demolition department, on 1/8/09, is flagged in both july and august.

How to I stop incidents that occur on the 1st of the month from being regarded as occurring in 2 months?

Many Thanks

Rich

p.s. if Donkeyote reads this, I know you said that countif is better then sumproduct but I coundnt get it to work for me!

Thanks for that JB, it worked great.

But I'm having trouble applying the new argument to other formulas in the sheet.

I have a column, "No. of Days Lost", in demolition sheet, with the forumla:

=SUMPRODUCT(--(Incidents!$A$4:$A$500=$A$1),--(Incidents!$C$4:$C$500>=$A9),--(Incidents!$C$4:$C$500<=DATE(YEAR($A9),MONTH($A9)+1,1)),Incidents!$I$4:$I$500)

(for July)

This column is different to the other cells because is counts the values in column I, rather then counting the instances of various incident codes.

I've tried inserting the new argument to ensure that incidents on the 1st of the month are only regarded to occur in one month rather then two, but keep getting an error message.

Rich

thanks DonkeyOte, youre a genius!

Sorry for posting my question. I didnt know that my post was still being discussed after JB's inital reply.

Thanks again

Rich