Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Occurences Between Dates
Hi All:
I have data that contains a variety of dates that I have sorted by date. I am trying to count the nuber of occurences in a given month. EI: Customer x 1/1/07 Customer x 1/1/07 Customer x 1/4/07 Customer x 1/5/07 Curtomer x 1/31/07 Total occurences for this customer would equal 5. I have been trying Countif function but it is not returning any value other than 0 or 1, so I believe that I am missing a step. I have looked through some of the previous posts but did not see anything that may pertain to what I am looking for. Thank you in advance for any advice you may offer. Blddrgn |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Occurences Between Dates
=SUMPRODUCT(--(A1:A100="Customer x"),--(MONTH(B1:B100)=1))
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Blddrgn700" wrote in message ... Hi All: I have data that contains a variety of dates that I have sorted by date. I am trying to count the nuber of occurences in a given month. EI: Customer x 1/1/07 Customer x 1/1/07 Customer x 1/4/07 Customer x 1/5/07 Curtomer x 1/31/07 Total occurences for this customer would equal 5. I have been trying Countif function but it is not returning any value other than 0 or 1, so I believe that I am missing a step. I have looked through some of the previous posts but did not see anything that may pertain to what I am looking for. Thank you in advance for any advice you may offer. Blddrgn |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Occurences Between Dates
On Mon, 22 Oct 2007 10:35:04 -0700, Blddrgn700
wrote: Hi All: I have data that contains a variety of dates that I have sorted by date. I am trying to count the nuber of occurences in a given month. EI: Customer x 1/1/07 Customer x 1/1/07 Customer x 1/4/07 Customer x 1/5/07 Curtomer x 1/31/07 Total occurences for this customer would equal 5. I have been trying Countif function but it is not returning any value other than 0 or 1, so I believe that I am missing a step. I have looked through some of the previous posts but did not see anything that may pertain to what I am looking for. Thank you in advance for any advice you may offer. Blddrgn =countif(a:a,"="&date(2007,1,1) - countif(a:a,""&date(2007,1,28) --ron |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Occurences Between Dates
"Bob Phillips" wrote: =SUMPRODUCT(--(A1:A100="Customer x"),--(MONTH(B1:B100)=1)) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Blddrgn700" wrote in message ... Hi All: I have data that contains a variety of dates that I have sorted by date. I am trying to count the nuber of occurences in a given month. EI: Customer x 1/1/07 Customer x 1/1/07 Customer x 1/4/07 Customer x 1/5/07 Curtomer x 1/31/07 Total occurences for this customer would equal 5. I have been trying Countif function but it is not returning any value other than 0 or 1, so I believe that I am missing a step. I have looked through some of the previous posts but did not see anything that may pertain to what I am looking for. Thank you in advance for any advice you may offer. Blddrgn Bob, Thank you I follow the information you provided. In my example I left out a valuable piece of information the date field is over two years 2006 and 2007. So I see in your formula month equals 1 that would count all first month data. Now with this being over two years were would the reference distinguish between those two years. I apolgize for leaving that crucial piece out in my original example. Blddrgn |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Occurences Between Dates
If you're always counting occurrences for an entire month...
try something like this: A1:A20 contains Customer Names B1:B20 contains dates C1: (a customer to search for) D1: (a date) This formula counts the occurrences of the customer in C1 where the Col_B date is in the same year and month as the date in D1: =SUMPRODUCT((A1:A20=C1)*(TEXT(B1:B20,"yyyymm")=TEX T(D1,"yyyymm"))) or if you prefer this method: =SUMPRODUCT(--(A1:A20=C1),--(TEXT(B1:B20,"yyyymm")=TEXT(D1,"yyyymm"))) Is that something you can work with? -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "Blddrgn700" wrote in message ... Hi All: I have data that contains a variety of dates that I have sorted by date. I am trying to count the nuber of occurences in a given month. EI: Customer x 1/1/07 Customer x 1/1/07 Customer x 1/4/07 Customer x 1/5/07 Curtomer x 1/31/07 Total occurences for this customer would equal 5. I have been trying Countif function but it is not returning any value other than 0 or 1, so I believe that I am missing a step. I have looked through some of the previous posts but did not see anything that may pertain to what I am looking for. Thank you in advance for any advice you may offer. Blddrgn |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Occurences Between Dates
"Ron Coderre" wrote: If you're always counting occurrences for an entire month... try something like this: A1:A20 contains Customer Names B1:B20 contains dates C1: (a customer to search for) D1: (a date) This formula counts the occurrences of the customer in C1 where the Col_B date is in the same year and month as the date in D1: =SUMPRODUCT((A1:A20=C1)*(TEXT(B1:B20,"yyyymm")=TEX T(D1,"yyyymm"))) or if you prefer this method: =SUMPRODUCT(--(A1:A20=C1),--(TEXT(B1:B20,"yyyymm")=TEXT(D1,"yyyymm"))) Is that something you can work with? -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "Blddrgn700" wrote in message ... Hi All: I have data that contains a variety of dates that I have sorted by date. I am trying to count the nuber of occurences in a given month. EI: Customer x 1/1/07 Customer x 1/1/07 Customer x 1/4/07 Customer x 1/5/07 Curtomer x 1/31/07 Total occurences for this customer would equal 5. I have been trying Countif function but it is not returning any value other than 0 or 1, so I believe that I am missing a step. I have looked through some of the previous posts but did not see anything that may pertain to what I am looking for. Thank you in advance for any advice you may offer. Blddrgn Ron, The second formula when I moved the outcome cell onto the same worksheet as the data. Thanks all who replied!!! Blddrgn700 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Occurences Between Dates
=SUMPRODUCT(--(A1:A100="Customer
x"),--(MONTH(B1:B100)=1),--(YEAR(B1:B100)=2007)) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Blddrgn700" wrote in message ... "Bob Phillips" wrote: =SUMPRODUCT(--(A1:A100="Customer x"),--(MONTH(B1:B100)=1)) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Blddrgn700" wrote in message ... Hi All: I have data that contains a variety of dates that I have sorted by date. I am trying to count the nuber of occurences in a given month. EI: Customer x 1/1/07 Customer x 1/1/07 Customer x 1/4/07 Customer x 1/5/07 Curtomer x 1/31/07 Total occurences for this customer would equal 5. I have been trying Countif function but it is not returning any value other than 0 or 1, so I believe that I am missing a step. I have looked through some of the previous posts but did not see anything that may pertain to what I am looking for. Thank you in advance for any advice you may offer. Blddrgn Bob, Thank you I follow the information you provided. In my example I left out a valuable piece of information the date field is over two years 2006 and 2007. So I see in your formula month equals 1 that would count all first month data. Now with this being over two years were would the reference distinguish between those two years. I apolgize for leaving that crucial piece out in my original example. Blddrgn |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting occurences of a name | Excel Worksheet Functions | |||
Counting the occurences | Excel Worksheet Functions | |||
Counting Occurences | Excel Discussion (Misc queries) | |||
Counting occurences of a specific day between two dates | Excel Worksheet Functions | |||
Counting Number of Occurences | Excel Discussion (Misc queries) |