Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count number of occurences on a particular date
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 for each status on each date (answer should be: New Open Fixed Closed 1 1 1 2 Please help, as I have spent too much time on this already! -- |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count number of occurences on a particular date
=COUNTIF(B2:B5="New") etc.
Dave -- Brevity is the soul of wit. " 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 for each status on each date (answer should be: New Open Fixed Closed 1 1 1 2 Please help, as I have spent too much time on this already! -- |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count number of occurences on a particular date
I put your little table "New Open Fixed Closed" into cell F1 to I1 and
into F2 I put the formula: =COUNTIF($B:$B,F1) and then I filled it across to I2. Cheers, Jason Lepack 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 for each status on each date (answer should be: New Open Fixed Closed 1 1 1 2 Please help, as I have spent too much time on this already! -- |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count number of occurences on a particular date
Sorry:
My brain must have been working faster than my fingers or vice versa anyway, what I am looking for is actually this. 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 Could someone help me please??? Thanks, -M Dave F wrote: =COUNTIF(B2:B5="New") etc. Dave -- Brevity is the soul of wit. " 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 for each status on each date (answer should be: New Open Fixed Closed 1 1 1 2 Please help, as I have spent too much time on this already! -- |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count number of occurences on a particular date
Sub OpenClosedTable()
Dim rM As Range, rC As Range, rO As Range, rT As Range Dim wsOld As Worksheet, wsNew As Worksheet Dim oCol As String, cCol As String ' Initialize Set wsOld = Sheets("sheet1") ' Comment this if you want to use the new sheet Set wsNew = Sheets("sheet1") wsNew.Range("F:I").Delete wsNew.Range("F1").Value = "Open" wsNew.Range("G1").Value = "Date" wsNew.Range("H1").Value = "Closed" wsNew.Range("I1").Value = "Date" 'intialize the pointers Set rC = wsNew.Range("H2") ' closed table Set rO = wsNew.Range("F2") ' open table Set rM = wsOld.Range("A1") ' main table oCol = "G:G" cCol = "I:I" ' End Here ' 'uncomment this if you want your reults in a new sheet ' Set wsNew = Sheets.Add ' wsNew.Range("A1").Value = "Open" ' wsNew.Range("B1").Value = "Date" ' wsNew.Range("C1").Value = "Closed" ' wsNew.Range("D1").Value = "Date" ' 'intialize the pointers ' Set rC = wsNew.Range("C2") ' closed table ' Set rO = wsNew.Range("A2") ' open table ' Set rM = wsOld.Range("A1") ' main table ' oCol = "B:B" ' cCol = "D:D" ' ' End Here ' format the dates Set rT = wsNew.Range(oCol & "," & cCol) rT.NumberFormat = "m/d/yyyy" ' start the job Do While Not rM.Value = "" ' if the list isn't contiguous - problem If rM.Offset(0, 1).Value = "Closed" Then Set rT = wsNew.Range(cCol) ' search for the current date in the main table Set rT = rT.Find(rM.Offset(0, 3)) If Not rT Is Nothing Then ' add one if it's found rT.Offset(0, -1) = rT.Offset(0, -1).Value + 1 Else ' add a new date if it's not rC.Value = 1 rC.Offset(0, 1).Value = rM.Offset(0, 3) Set rC = rC.Offset(1, 0) End If Else Set rT = wsNew.Range(oCol) Set rT = rT.Find(rM.Offset(0, 2)) If Not rT Is Nothing Then rT.Offset(0, -1) = rT.Offset(0, -1).Value + 1 Else rO.Value = 1 rO.Offset(0, 1).Value = rM.Offset(0, 2) Set rO = rO.Offset(1, 0) End If End If Set rM = rM.Offset(1, 0) ' next record Loop ' clean up Set rT = Nothing Set rC = Nothing Set rO = Nothing Set rM = Nothing Set wsOld = Nothing Set wsNew = Nothing End Sub wrote: Sorry: My brain must have been working faster than my fingers or vice versa anyway, what I am looking for is actually this. 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 Could someone help me please??? Thanks, -M Dave F wrote: =COUNTIF(B2:B5="New") etc. Dave -- Brevity is the soul of wit. " 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 for each status on each date (answer should be: New Open Fixed Closed 1 1 1 2 Please help, as I have spent too much time on this already! -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Mileage Claim Formula | New Users to Excel | |||
count between start date and end date | Excel Discussion (Misc queries) | |||
count the number of cells within a column with a date <= today's d | New Users to Excel | |||
Where is DateDiff function in Excel 2002 ? | Excel Worksheet Functions | |||
Count number to reach a cumulative value | Excel Worksheet Functions |