Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count occurances | Excel Worksheet Functions | |||
Count of different occurances | Excel Discussion (Misc queries) | |||
Count Occurances of a txt string | Excel Discussion (Misc queries) | |||
count occurances | Excel Discussion (Misc queries) | |||
how to keep previous months' data | Excel Worksheet Functions |