Count occurances of a name over previous 12 months
 

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

Count occurances of a name over previous 12 months
 

=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)

" wrote in message
...
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

Count occurances of a name over previous 12 months
 

=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

Count occurances of a name over previous 12 months
 

On Jun 30, 2:35*pm, "Bob Phillips" 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)

" wrote in message

...



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

Count occurances of a name over previous 12 months
 

=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)

" wrote in message
...
On Jun 30, 2:35 pm, "Bob Phillips" 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)

" wrote in message

...



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