Calculate number of occurences with last 3 and 6 months

**jpruffle** · 07-14-2009, 02:43 PM

Hi,

I have a row containing:

Date entered
Name
Date of Birth (DOB)
Occurrences of Name + DOB this Month
Occurrences of Name + DOB within last 3 Months
Occurrences of Name + DOB within last 6 Months

I can manage the occurrences this month but haven't a clue how to calculate the 3 and 6 months ones. I have attached a sheet and would appreciate some guidance and help.

Thank you in advance.

**NBVC** · 07-14-2009, 02:52 PM

Try:

=SUMPRODUCT((name=D2)*(dob=E2)*(MONTH(month)>=MONTH(C2)-3)*(month<>""))

and

=SUMPRODUCT((name=D2)*(dob=E2)*(MONTH(month)>=MONTH(C2)-6)*(month<>""))

**jpruffle** · 07-14-2009, 03:07 PM

Thank you for the quick reply.

I have added the calculations but they don't seem to stack up?

I've highlighted where in this sheet.

Could you possibly have a look, sure I am missing something glaringly obvious.

Thank you once again.

**NBVC** · 07-14-2009, 03:17 PM

Your date in E13 is not formatted the same as the rest, please retype the date and make sure it is the same format...

Then, assuming for last 3 months you want to exclude "this Months"... then possibly formula in G2 copied down:

=SUMPRODUCT((name=D2)*(dob=E2)*(MONTH(month)>=MONTH(C2)-3)*(month<>""))-F2

and in H2, for 6 months less this months...

=SUMPRODUCT((name=D2)*(dob=E2)*(MONTH(month)>=MONTH(C2)-6)*(month<>""))-F2

I got 3, 5 and 7 for the last Josh Simpson entry??