Results 1 to 4 of 4

Thread: sum data between two cell values
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Apr 2007
    Posts
    129
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default sum data between two cell values

    Hi All,

    I know this is a question like using Sumproduct to total data between dates but i just cant get this to work.

    Each line in the report shows a status change with in a record there are multiple records in the report, also there are random combinations of status changes per record. Im trying to sum the minutes in duration between two of the status types.

    example data in cols A B C. A is the record ID B is the Status value and C is the duration for each status.
    INC0001, Start, 15
    INC0001, Process1, 5
    INC0001, Process2, 11
    INC0001, Process4, 6
    INC0001, Process1, 5
    INC0001, End, 7
    INC0001, Refresh, 5
    INC0002, Start, 15
    INC0002, Process1, 5
    INC0002, Process2, 14
    INC0002, Process1, 5
    INC0002, End, 7

    So I need to calc the duration for INC0001 between Start and End points giving a total of 49 and INC0002 total of 46. the sumproduct example i have is for date ranges which ive tried to modify by unsure how to combine the INC ref and the status and then add up between those points.

    Cheers
    Hello have some formula fun in excel

  2. #2
    MrExcel MVP
    Moderator
    Scott Huish's Avatar
    Join Date
    Mar 2004
    Location
    Oregon
    Posts
    19,734
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    4 Thread(s)

    Default Re: sum data between two cell values

    Why does INC0001 not total to 54?

    Why not use a Pivot Table?

    http://www.howtogeek.com/howto/6416/...in-excel-2007/
    Office 2010/365

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,091
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: sum data between two cell values

    ID Status Duration INC0001
    INC0001 Start 15 Start
    INC0001 Process1 5 End
    INC0001 Process2 11 49
    INC0001 Process4 6
    INC0001 Process1 5
    INC0001 End 7
    INC0001 Refresh 5
    INC0002 Start 15
    INC0002 Process1 5
    INC0002 Process2 14
    INC0002 Process1 5
    INC0002 End 7

    Not sure if this is possible/easy with a pivot table. What follows is a formula approach...

    F4, control+shift+enter, not just enter:
    Code:
    =SUM(
      IF(ROW($B$2:$B$13)-ROW(B$2)+1>=MATCH(F2,IF($A$2:$A$13=F1,$B$2:$B$13),0),
      IF(ROW($B$2:$B$13)-ROW(B$2)+1<=MATCH(F3,IF($A$2:$A$13=F1,$B$2:$B$13),0),
      IF($A$2:$A$13=F1,$C$2:$C$13))))
    
    Assuming too much and qualifying too much are two faces of the same problem.

  4. #4
    Board Regular
    Join Date
    Apr 2007
    Posts
    129
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: sum data between two cell values

    Many thnaks will test and return here with my results
    Hello have some formula fun in excel

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •