Counting by grouping cells

V

vito

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
 
B

bj

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.
 
V

vito

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
 
P

PCLIVE

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


--
 
P

PCLIVE

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.

--
 
V

vito

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
 
P

PCLIVE

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


--
 
V

vito

Thanks worked great.

PCLIVE said:
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
Click to expand...
 
Top