Counting by grouping cells
 

Hi,
I have a list of dates I need to count. However, I need to count them by
groupings of one day a part. In other words if there are three dates 7/18
7/19 & 7/20 that would be 1. There may be groups of one day or several. Any
ideas would be great.
Thanks

Counting by grouping cells
 

need more details on what you are trying to do.
what is the format of your spread sheet?
how are the groups to be counted as one identified?
are the groups separated by an empty cell, etc.

"vito" wrote:

Hi,
I have a list of dates I need to count. However, I need to count them by
groupings of one day a part. In other words if there are three dates 7/18
7/19 & 7/20 that would be 1. There may be groups of one day or several. Any
ideas would be great.
Thanks

Counting by grouping cells
 

The groups are not seperated. It is a list of sequential dates. For example;
in the list below there are 2 sets of dates for cleaning. What I need is a
function which will count the # of these cleanings between a given date
range. Therefore, if I was looking at 7/1/06 - 7/31/06 clean = 1.

Date Cleaned
7/18/06
7/19/06
8/31/06
9/1/06
9/2/06
9/3/06
9/4/06
9/5/06


"bj" wrote:

need more details on what you are trying to do.
what is the format of your spread sheet?
how are the groups to be counted as one identified?
are the groups separated by an empty cell, etc.

"vito" wrote:

Hi,
I have a list of dates I need to count. However, I need to count them by
groupings of one day a part. In other words if there are three dates 7/18
7/19 & 7/20 that would be 1. There may be groups of one day or several. Any
ideas would be great.
Thanks

Counting by grouping cells
 

Where B1 contains the beginning date and C1 contains the ending date from
between which you want to count the number of dates.

=SUMPRODUCT(--(A1:A8=B1),--(A1:A8<=C1))

HTH,
Paul


--

"vito" wrote in message
...
The groups are not seperated. It is a list of sequential dates. For
example;
in the list below there are 2 sets of dates for cleaning. What I need is a
function which will count the # of these cleanings between a given date
range. Therefore, if I was looking at 7/1/06 - 7/31/06 clean = 1.

Date Cleaned
7/18/06
7/19/06
8/31/06
9/1/06
9/2/06
9/3/06
9/4/06
9/5/06


"bj" wrote:

need more details on what you are trying to do.
what is the format of your spread sheet?
how are the groups to be counted as one identified?
are the groups separated by an empty cell, etc.

"vito" wrote:

Hi,
I have a list of dates I need to count. However, I need to count them
by
groupings of one day a part. In other words if there are three dates
7/18
7/19 & 7/20 that would be 1. There may be groups of one day or several.
Any
ideas would be great.
Thanks

Counting by grouping cells
 

I just realized that my suggestion isn't going to give you what you want as
you want to count each consecutive group as one.

--

"PCLIVE" wrote in message
...
Where B1 contains the beginning date and C1 contains the ending date from
between which you want to count the number of dates.

=SUMPRODUCT(--(A1:A8=B1),--(A1:A8<=C1))

HTH,
Paul


--

"vito" wrote in message
...
The groups are not seperated. It is a list of sequential dates. For
example;
in the list below there are 2 sets of dates for cleaning. What I need is
a
function which will count the # of these cleanings between a given date
range. Therefore, if I was looking at 7/1/06 - 7/31/06 clean = 1.

Date Cleaned
7/18/06
7/19/06
8/31/06
9/1/06
9/2/06
9/3/06
9/4/06
9/5/06


"bj" wrote:

need more details on what you are trying to do.
what is the format of your spread sheet?
how are the groups to be counted as one identified?
are the groups separated by an empty cell, etc.

"vito" wrote:

Hi,
I have a list of dates I need to count. However, I need to count them
by
groupings of one day a part. In other words if there are three dates
7/18
7/19 & 7/20 that would be 1. There may be groups of one day or
several. Any
ideas would be great.
Thanks

Counting by grouping cells
 

This works for counting the number of days out for service, but not the
number of times out for service. I want to be able to treat consecutive days
as a single occurance.

Vito

"PCLIVE" wrote:

Where B1 contains the beginning date and C1 contains the ending date from
between which you want to count the number of dates.

=SUMPRODUCT(--(A1:A8=B1),--(A1:A8<=C1))

HTH,
Paul


--

"vito" wrote in message
...
The groups are not seperated. It is a list of sequential dates. For
example;
in the list below there are 2 sets of dates for cleaning. What I need is a
function which will count the # of these cleanings between a given date
range. Therefore, if I was looking at 7/1/06 - 7/31/06 clean = 1.

Date Cleaned
7/18/06
7/19/06
8/31/06
9/1/06
9/2/06
9/3/06
9/4/06
9/5/06


"bj" wrote:

need more details on what you are trying to do.
what is the format of your spread sheet?
how are the groups to be counted as one identified?
are the groups separated by an empty cell, etc.

"vito" wrote:

Hi,
I have a list of dates I need to count. However, I need to count them
by
groupings of one day a part. In other words if there are three dates
7/18
7/19 & 7/20 that would be 1. There may be groups of one day or several.
Any
ideas would be great.
Thanks

Counting by grouping cells
 

I'm sure there is probably another way, but you could use a helper column to
determine the number of days between each date.

With your data starting in A1:
In column D starting at D2, enter this formula.
=A2-A1

Copy this formula down through A8, or to whatever row your data in column A
ends.

Then use this formula:
=SUMPRODUCT(--(A1:A8=B1),--(A1:A8<=C1),--(D1:D8<1))

HTH,
Paul


--

"PCLIVE" wrote in message
...
I just realized that my suggestion isn't going to give you what you want as
you want to count each consecutive group as one.

--

"PCLIVE" wrote in message
...
Where B1 contains the beginning date and C1 contains the ending date from
between which you want to count the number of dates.

=SUMPRODUCT(--(A1:A8=B1),--(A1:A8<=C1))

HTH,
Paul


--

"vito" wrote in message
...
The groups are not seperated. It is a list of sequential dates. For
example;
in the list below there are 2 sets of dates for cleaning. What I need is
a
function which will count the # of these cleanings between a given date
range. Therefore, if I was looking at 7/1/06 - 7/31/06 clean = 1.

Date Cleaned
7/18/06
7/19/06
8/31/06
9/1/06
9/2/06
9/3/06
9/4/06
9/5/06


"bj" wrote:

need more details on what you are trying to do.
what is the format of your spread sheet?
how are the groups to be counted as one identified?
are the groups separated by an empty cell, etc.

"vito" wrote:

Hi,
I have a list of dates I need to count. However, I need to count them
by
groupings of one day a part. In other words if there are three dates
7/18
7/19 & 7/20 that would be 1. There may be groups of one day or
several. Any
ideas would be great.
Thanks

Counting by grouping cells
 

Thanks worked great.

"PCLIVE" wrote:

I'm sure there is probably another way, but you could use a helper column to
determine the number of days between each date.

With your data starting in A1:
In column D starting at D2, enter this formula.
=A2-A1

Copy this formula down through A8, or to whatever row your data in column A
ends.

Then use this formula:
=SUMPRODUCT(--(A1:A8=B1),--(A1:A8<=C1),--(D1:D8<1))

HTH,
Paul


--

"PCLIVE" wrote in message
...
I just realized that my suggestion isn't going to give you what you want as
you want to count each consecutive group as one.

--

"PCLIVE" wrote in message
...
Where B1 contains the beginning date and C1 contains the ending date from
between which you want to count the number of dates.

=SUMPRODUCT(--(A1:A8=B1),--(A1:A8<=C1))

HTH,
Paul


--

"vito" wrote in message
...
The groups are not seperated. It is a list of sequential dates. For
example;
in the list below there are 2 sets of dates for cleaning. What I need is
a
function which will count the # of these cleanings between a given date
range. Therefore, if I was looking at 7/1/06 - 7/31/06 clean = 1.

Date Cleaned
7/18/06
7/19/06
8/31/06
9/1/06
9/2/06
9/3/06
9/4/06
9/5/06


"bj" wrote:

need more details on what you are trying to do.
what is the format of your spread sheet?
how are the groups to be counted as one identified?
are the groups separated by an empty cell, etc.

"vito" wrote:

Hi,
I have a list of dates I need to count. However, I need to count them
by
groupings of one day a part. In other words if there are three dates
7/18
7/19 & 7/20 that would be 1. There may be groups of one day or
several. Any
ideas would be great.
Thanks