Summary Help Needed
in [Programming]
|
From: SIRSTEVE on 15 Apr 2010 10:36 Here are the fields in my spread sheet. Date Name Request Received Start Time Finish Time Total Time Here's an example of what I'm trying to accomplish. Let's say I need to see how much time John has spent doing research mail. I turn on the auto filter feature so that I can select only information pertaining to John. Ok. Question, how can I create a summary that will give me a total at the end of the report of how much time John has spent?
From: Ron de Bruin on 15 Apr 2010 10:47 See the help for the subtotal worksheet function -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "SIRSTEVE" <SIRSTEVE(a)discussions.microsoft.com> wrote in message news:FB8D9970-4829-44E0-A076-EF2A85176472(a)microsoft.com... > Here are the fields in my spread sheet. > > Date > Name > Request Received > Start Time > Finish Time > Total Time > > Here's an example of what I'm trying to accomplish. > > Let's say I need to see how much time John has spent doing research mail. > I turn on the auto filter feature so that I can select only information > pertaining to John. Ok. Question, how can I create a summary that will give > me a total at the end of the report of how much time John has spent?
From: JLGWhiz on 15 Apr 2010 10:49 If columns are like: A B C D E F 4/15 John --- --- --- 2 = SumIf(B2:B100, "John", F2:F100) This would give you a total of John's hours. The range size is for illustaration only. You could adjust it for the actual size. "SIRSTEVE" <SIRSTEVE(a)discussions.microsoft.com> wrote in message news:FB8D9970-4829-44E0-A076-EF2A85176472(a)microsoft.com... > Here are the fields in my spread sheet. > > Date > Name > Request Received > Start Time > Finish Time > Total Time > > Here's an example of what I'm trying to accomplish. > > Let's say I need to see how much time John has spent doing research mail. > I turn on the auto filter feature so that I can select only information > pertaining to John. Ok. Question, how can I create a summary that will > give > me a total at the end of the report of how much time John has spent?
From: Bernard Liengme on 15 Apr 2010 10:59 I should have added: you do not need to filter when using the SUMPRODUCT formula or pivot tables Bernard "SIRSTEVE" <SIRSTEVE(a)discussions.microsoft.com> wrote in message news:FB8D9970-4829-44E0-A076-EF2A85176472(a)microsoft.com... > Here are the fields in my spread sheet. > > Date > Name > Request Received > Start Time > Finish Time > Total Time > > Here's an example of what I'm trying to accomplish. > > Let's say I need to see how much time John has spent doing research mail. > I turn on the auto filter feature so that I can select only information > pertaining to John. Ok. Question, how can I create a summary that will > give > me a total at the end of the report of how much time John has spent?
From: Bernard Liengme on 15 Apr 2010 10:58
I am going to assume that your fields are in column A (date) thru F (total time) That row 1 has labels and rows 2 to 200 have your data In K1:M1 enter headers: Name, Request, Time In K2 type the name to be found, e.g. John In L2 type the request: e.g. Research Mail In M2 enter this formula =SUMPRODUCT(--(A2:A200=K2),--(C2:C200=L2), F2:F200) For more details on SUMPRODUCT Bob Phillips http://www.xldynamic.com/source/xld.SUMPRODUCT.html J.E McGimpsey http://mcgimpsey.com/excel/formulae/doubleneg.html Debra Dalgleish http://www.contextures.com/xlFunctions04.html#SumProduct If you need a better approach take a big breath and learn about Pivot Tables. A bit scary at first but well worth the effort it you have may reports like this. Here is where to start: Debra Dalgleish's pictures at Jon Peltier's site: http://peltiertech.com/Excel/Pivots/pivottables.htm And Debra's own site: http://www.contextures.com/xlPivot01.html John Walkenbach also has some at: http://j-walk.com/ss/excel/files/general.htm (look for Tony Gwynn's Hit Database) Chip Pearson keeps Harald Staff's notes at: http://www.cpearson.com/excel/pivots.htm best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "SIRSTEVE" <SIRSTEVE(a)discussions.microsoft.com> wrote in message news:FB8D9970-4829-44E0-A076-EF2A85176472(a)microsoft.com... > Here are the fields in my spread sheet. > > Date > Name > Request Received > Start Time > Finish Time > Total Time > > Here's an example of what I'm trying to accomplish. > > Let's say I need to see how much time John has spent doing research mail. > I turn on the auto filter feature so that I can select only information > pertaining to John. Ok. Question, how can I create a summary that will > give > me a total at the end of the report of how much time John has spent? |