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

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

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

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

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

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

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