Hello all:

I have a spreadsheet where I want to count the occurences of a value
on a certain date and then use the resulting information in a chart.


My spreadsheet looks someting like this:


Column *A* contains the ID of the defect
Column *B* contains the status
Column *E* contains the open date
Column *F* contains the closing date if closed.


An example spreadsheet would be:


123 Closed 05/12/2006 07/24/2006
100 Open 04/30/2006
125 Fixed 05/12/2006
180 New 09/22/2006
140 Closed 07/01/2006 07/24/2006


I would like to:


*a)* count the number of defects that are open on each date... New Open
and Fixed are considered Open defects (answer
should be:


Open Date
1 04/30/2006
1 05/12/2006
1 09/22/2006


*b)* count the number of closed defects for a particular date,

Closed Date
2 07/24/2006


Please help, as I have spent too much time on this already!

Hi

I think a pivot table will do the needful for you.

I am assuming that you need just the count which a pivot table can
provide.

If you need to have the list of open, fixed or new dates, you can click
on the COUNT value in the pivot table to get the list of records too.

Before you create the pivot table, you can also consider adding a
column that gives a value like "ALL OPEN" to your fixed, new and open
records.

Hope this is useful.

Best
Prasad


M.Spe...@gmail.com wrote:
> Hello all:
>
> I have a spreadsheet where I want to count the occurences of a value
> on a certain date and then use the resulting information in a chart.
>
>
> My spreadsheet looks someting like this:
>
>
> Column *A* contains the ID of the defect
> Column *B* contains the status
> Column *E* contains the open date
> Column *F* contains the closing date if closed.
>
>
> An example spreadsheet would be:
>
>
> 123 Closed 05/12/2006 07/24/2006
> 100 Open 04/30/2006
> 125 Fixed 05/12/2006
> 180 New 09/22/2006
> 140 Closed 07/01/2006 07/24/2006
>
>
> I would like to:
>
>
> *a)* count the number of defects that are open on each date... New Open
> and Fixed are considered Open defects (answer
> should be:
>
>
> Open Date
> 1 04/30/2006
> 1 05/12/2006
> 1 09/22/2006
>
>
> *b)* count the number of closed defects for a particular date,
>
> Closed Date
> 2 07/24/2006
>
>
> Please help, as I have spent too much time on this already!

Hi

I think a pivot table will do the needful for you.

I am assuming that you need just the count which a pivot table can
provide.

If you need to have the list of open, fixed or new dates, you can click
on the COUNT value in the pivot table to get the list of records too.

Before you create the pivot table, you can also consider adding a
column that gives a value like "ALL OPEN" to your fixed, new and open
records.

Hope this is useful.

Best
Prasad


M.Spe...@gmail.com wrote:
> Hello all:
>
> I have a spreadsheet where I want to count the occurences of a value
> on a certain date and then use the resulting information in a chart.
>
>
> My spreadsheet looks someting like this:
>
>
> Column *A* contains the ID of the defect
> Column *B* contains the status
> Column *E* contains the open date
> Column *F* contains the closing date if closed.
>
>
> An example spreadsheet would be:
>
>
> 123 Closed 05/12/2006 07/24/2006
> 100 Open 04/30/2006
> 125 Fixed 05/12/2006
> 180 New 09/22/2006
> 140 Closed 07/01/2006 07/24/2006
>
>
> I would like to:
>
>
> *a)* count the number of defects that are open on each date... New Open
> and Fixed are considered Open defects (answer
> should be:
>
>
> Open Date
> 1 04/30/2006
> 1 05/12/2006
> 1 09/22/2006
>
>
> *b)* count the number of closed defects for a particular date,
>
> Closed Date
> 2 07/24/2006
>
>
> Please help, as I have spent too much time on this already!

Hi

I think a pivot table will do the needful for you.

I am assuming that you need just the count which a pivot table can
provide.

If you need to have the list of open, fixed or new dates, you can click
on the COUNT value in the pivot table to get the list of records too.

Before you create the pivot table, you can also consider adding a
column that gives a value like "ALL OPEN" to your fixed, new and open
records.

Hope this is useful.

Best
Prasad


M.Spe...@gmail.com wrote:
> Hello all:
>
> I have a spreadsheet where I want to count the occurences of a value
> on a certain date and then use the resulting information in a chart.
>
>
> My spreadsheet looks someting like this:
>
>
> Column *A* contains the ID of the defect
> Column *B* contains the status
> Column *E* contains the open date
> Column *F* contains the closing date if closed.
>
>
> An example spreadsheet would be:
>
>
> 123 Closed 05/12/2006 07/24/2006
> 100 Open 04/30/2006
> 125 Fixed 05/12/2006
> 180 New 09/22/2006
> 140 Closed 07/01/2006 07/24/2006
>
>
> I would like to:
>
>
> *a)* count the number of defects that are open on each date... New Open
> and Fixed are considered Open defects (answer
> should be:
>
>
> Open Date
> 1 04/30/2006
> 1 05/12/2006
> 1 09/22/2006
>
>
> *b)* count the number of closed defects for a particular date,
>
> Closed Date
> 2 07/24/2006
>
>
> Please help, as I have spent too much time on this already!

F G H
date open closed
5/12/2006 1 0
4/30/2006 1 0
5/12/2006 1 0
9/22/2006 1 0
7/1/2006 0 0
7/24/2006 0 2

col g and copied down where f9 is the first date
=SUMPRODUCT(($B$2:$B$6={"open","fixed","new"})*($E$2:$E$6=F9))
col H
=SUMPRODUCT(($B$2:$B$6="closed")*($F$2:$F$6=F9))
--
Don Guillett
SalesAid Software
(E-Mail Removed)
<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hello all:
>
> I have a spreadsheet where I want to count the occurences of a value
> on a certain date and then use the resulting information in a chart.
>
>
> My spreadsheet looks someting like this:
>
>
> Column *A* contains the ID of the defect
> Column *B* contains the status
> Column *E* contains the open date
> Column *F* contains the closing date if closed.
>
>
> An example spreadsheet would be:
>
>
> 123 Closed 05/12/2006 07/24/2006
> 100 Open 04/30/2006
> 125 Fixed 05/12/2006
> 180 New 09/22/2006
> 140 Closed 07/01/2006 07/24/2006
>
>
> I would like to:
>
>
> *a)* count the number of defects that are open on each date... New Open
> and Fixed are considered Open defects (answer
> should be:
>
>
> Open Date
> 1 04/30/2006
> 1 05/12/2006
> 1 09/22/2006
>
>
> *b)* count the number of closed defects for a particular date,
>
> Closed Date
> 2 07/24/2006
>
>
> Please help, as I have spent too much time on this already!
>

Don Guillett wrote:
> F G H
> date open closed
> 5/12/2006 1 0
> 4/30/2006 1 0
> 5/12/2006 1 0
> 9/22/2006 1 0
> 7/1/2006 0 0
> 7/24/2006 0 2
>
> col g and copied down where f9 is the first date
> =SUMPRODUCT(($B$2:$B$6={"open","fixed","new"})*($E$2:$E$6=F9))
> col H
> =SUMPRODUCT(($B$2:$B$6="closed")*($F$2:$F$6=F9))
> --
> Don Guillett
> SalesAid Software
> (E-Mail Removed)
> <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > Hello all:
> >
> > I have a spreadsheet where I want to count the occurences of a value
> > on a certain date and then use the resulting information in a chart.
> >
> >
> > My spreadsheet looks someting like this:
> >
> >
> > Column *A* contains the ID of the defect
> > Column *B* contains the status
> > Column *E* contains the open date
> > Column *F* contains the closing date if closed.
> >
> >
> > An example spreadsheet would be:
> >
> >
> > 123 Closed 05/12/2006 07/24/2006
> > 100 Open 04/30/2006
> > 125 Fixed 05/12/2006
> > 180 New 09/22/2006
> > 140 Closed 07/01/2006 07/24/2006
> >
> >
> > I would like to:
> >
> >
> > *a)* count the number of defects that are open on each date... New Open
> > and Fixed are considered Open defects (answer
> > should be:
> >
> >
> > Open Date
> > 1 04/30/2006
> > 1 05/12/2006
> > 1 09/22/2006
> >
> >
> > *b)* count the number of closed defects for a particular date,
> >
> > Closed Date
> > 2 07/24/2006
> >
> >
> > Please help, as I have spent too much time on this already!
> >