Hi

I have a spreadsheet to track operator movements etc
Col A lists the name of the operators
Col B lists the date of an event relating to a particular operator

What I need to be able to do is count back 12 months from the date I
enter in col B and count the number of occurances of the name I
entered in Col A

Eg if Joe Bloggs name is entered on 30/06/08 I need to know how many
time Joe Bloggs appears between 30/06/08 and 30/06/07

Hope this makes sense and you can offer some suggestions

Thanks
Martin

=SUMPRODUCT(--($A$2:$A$200=A2),--($B$2:$B$200<=B2),--($B$2:$B$200>=DATE(YEAR(B2)-1,MONTH(B2),DAY(B2))))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"(E-Mail Removed)" <(E-Mail Removed)> wrote in message
news:f6b8f331-42d1-4662-9162-(E-Mail Removed)...
> Hi
>
> I have a spreadsheet to track operator movements etc
> Col A lists the name of the operators
> Col B lists the date of an event relating to a particular operator
>
> What I need to be able to do is count back 12 months from the date I
> enter in col B and count the number of occurances of the name I
> entered in Col A
>
> Eg if Joe Bloggs name is entered on 30/06/08 I need to know how many
> time Joe Bloggs appears between 30/06/08 and 30/06/07
>
> Hope this makes sense and you can offer some suggestions
>
> Thanks
> Martin

=SUM(IF((A1:A1000="Joe
Bolggs")*(B1:B1000>="30/06/07")*(B1:B1000=<"30/06/08"),1,0))

array-enter it i.e. CTRL+SHIFT+ENTER

On Jun 30, 2:35*pm, "Bob Phillips" <bob....@somewhere.com> wrote:
> =SUMPRODUCT(--($A$2:$A$200=A2),--($B$2:$B$200<=B2),--($B$2:$B$200>=DATE(YEA*R(B2)-1,MONTH(B2),DAY(B2))))
>
> --
> HTH
>
> Bob
>
> (there's no email, no snail mail, but somewhere should be gmail in my addy)
>
> "durb...@googlemail.com" <mn.bags...@btinternet.com> wrote in message
>
> news:f6b8f331-42d1-4662-9162-(E-Mail Removed)...
>
>
>
> > Hi
>
> > I have a spreadsheet to track operator movements etc
> > Col A lists the name of the operators
> > Col B lists the date of an event relating to a particular operator
>
> > What I need to be able to do is count back 12 months from the date I
> > enter in col B and count the number of occurances of the name I
> > entered in Col A
>
> > Eg if Joe Bloggs name is entered on 30/06/08 I need to know how many
> > time Joe Bloggs appears between 30/06/08 and 30/06/07
>
> > Hope this makes sense and you can offer some suggestions
>
> > Thanks
> > Martin- Hide quoted text -
>
> - Show quoted text -

Thanks Bob

That does exactly what I need but there is one final question, suppose
Joe Bloggs has 6 occurances in the last 12 months. In Col K 2
occurances could have the text "wa" against them. I need to ignore and
subract these 2 results and only count 4 of the ocurances.

This is the last question honest

Regards
Martin

=SUMPRODUCT(--($A$2:$A$200=A2),
--($B$2:$B$200<=B2),--($B$2:$B$200>=DATE(YEA*R(B2)-1,MONTH(B2),DAY(B2))),
--($K$2:$K$200<>"wa"))


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"(E-Mail Removed)" <(E-Mail Removed)> wrote in message
news:83f1c7e6-52e0-430a-ae61-(E-Mail Removed)...
On Jun 30, 2:35 pm, "Bob Phillips" <bob....@somewhere.com> wrote:
> =SUMPRODUCT(--($A$2:$A$200=A2),--($B$2:$B$200<=B2),--($B$2:$B$200>=DATE(YEA*R(B2)-1,MONTH(B2),DAY(B2))))
>
> --
> HTH
>
> Bob
>
> (there's no email, no snail mail, but somewhere should be gmail in my
> addy)
>
> "durb...@googlemail.com" <mn.bags...@btinternet.com> wrote in message
>
> news:f6b8f331-42d1-4662-9162-(E-Mail Removed)...
>
>
>
> > Hi
>
> > I have a spreadsheet to track operator movements etc
> > Col A lists the name of the operators
> > Col B lists the date of an event relating to a particular operator
>
> > What I need to be able to do is count back 12 months from the date I
> > enter in col B and count the number of occurances of the name I
> > entered in Col A
>
> > Eg if Joe Bloggs name is entered on 30/06/08 I need to know how many
> > time Joe Bloggs appears between 30/06/08 and 30/06/07
>
> > Hope this makes sense and you can offer some suggestions
>
> > Thanks
> > Martin- Hide quoted text -
>
> - Show quoted text -

Thanks Bob

That does exactly what I need but there is one final question, suppose
Joe Bloggs has 6 occurances in the last 12 months. In Col K 2
occurances could have the text "wa" against them. I need to ignore and
subract these 2 results and only count 4 of the ocurances.

This is the last question honest

Regards
Martin