count number of occurences on a particular date

[M.Speare]

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!

 

[excelexpert1973]

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

 

[excelexpert1973]

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

 

[excelexpert1973]

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

 

[Don Guillett]

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))

 

[M.Speare]

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))