 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / December 2006count 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 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: > [quoted text clipped - 33 lines] > > 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: > [quoted text clipped - 33 lines] > > 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: > [quoted text clipped - 33 lines] > > 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))  SignatureDon Guillett SalesAid Software dguillett1@austin.rr.com > Hello all: > [quoted text clipped - 33 lines] > > Please help, as I have spent too much time on this already! > F G H > date open closed [quoted text clipped - 50 lines] > > > > Please help, as I have spent too much time on this already! |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.