Results 1 to 10 of 10

Thread: Formula for Summing A Streak and Restart The Sum after Streak Ends
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jun 2010
    Posts
    59
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Formula for Summing A Streak and Restart The Sum after Streak Ends

    Hello, I want to try and sum a streak for both positive and negative numbers, but once the streak stops, it will restart a new sum. As you can see in row B2, If I input a positive number, I want a formula to sum it in B3, if it is a negative number, it will sum it in B4.

    In cell D3, it sums a total of 13 which was the total sum of positive numbers before the streak ended. Then there is a negative streak that ends in cell H4, the positive streak resumes in cell I3 and starts a new sum count, it does not include the streak in cell B3:D3

    Is there a formula I can input in cell B3 and B4, I donít want the formula to include the total in cell G3, G4, M3, M4. I would like the formula to ignore these cells because I just want the streak sum from Monday to Friday calculated carrying over the the next week as seen in the example


    A B C D E F G H I J K L M
    1 Monday Tuesday Wednesday Thursday Friday Total Monday Tuesday Wednesday Thursday Friday Total
    2 input Number 5 5 3 -5 -5 -5 4 5 5 -5
    3 positive streak 5 10 13

    13
    4 9 14
    14
    4 negative streak -5 -10 -10 -15

    -5
    -10


    Thank you for taking the time to read this, look forward to hearing from you.

    Best Regards,

    Vlad

  2. #2
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,085
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Formula for Summing A Streak and Restart The Sum after Streak Ends

    Don't understand the results you showed in row 4 (negative streak)

    Is this what you want?

    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    M
    1
    Monday
    Tuesday
    Wednesday
    Thursday
    Friday
    Total
    Monday
    Tuesday
    Wednesday
    Thursday
    Friday
    Total
    2
    input Number
    5
    5
    3
    -5
    -5
    -5
    4
    5
    5
    -5
    3
    positive streak
    5
    10
    13
    13
    4
    9
    14
    14
    4
    negative streak
    -5
    -10
    -10
    -5
    -10
    -10


    M.

  3. #3
    Board Regular
    Join Date
    Jun 2010
    Posts
    59
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula for Summing A Streak and Restart The Sum after Streak Ends

    hi Marcelo,

    Well, in Cell G4 shows the total for that week, monday to friday, it was -10, then on monday in cell H2, it was -5, so the streak was still negative , E2, F2, so it was added to the total of -10 in cell G4, so now it became -15 in cell H4 because the numbers inputted were negative on thursday -5, friday -5, then the following monday -5, which was a negative streak of -15., basically the streak carried over to the next week until the streak ended with a positive 4 in cell I2.....

  4. #4
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,085
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Formula for Summing A Streak and Restart The Sum after Streak Ends

    Post 1
    I don't understand why K4 (K2 is positive) is equal to -5 and M4 is equal to -10?

    M.

  5. #5
    Board Regular
    Join Date
    Jun 2010
    Posts
    59
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula for Summing A Streak and Restart The Sum after Streak Ends

    hi Marcelo,

    yes, sorry the -5 is K4 should be in L4, and there should be nothing in cell k4 the reason cell M4 equals -10 is because it is calculating on that weeks sum from monday to friday, there was a -5 on monday in H2 and -5 on friday in cell L2, so the total would be -10 for that week in that week, the totals have nothing to do with the streak, they only measure what the total is for that week, the streak is on going which carries over week to week, as you see in cell E2, F2 and H2, there was a negative streak of 3 days which was thursday, friday and the next monday which carried over, that streak equaled -15, so the streaks carry over whereas the totals, i just sum the positive and negative sum for that week, the total formula is not important, its the streak that i cant seem to find a formula for...

  6. #6
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,085
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Formula for Summing A Streak and Restart The Sum after Streak Ends

    Still not clear (at least for me)
    Please, could you show us the expected results for row 3 (positive streak) and row 4 (negative streak)?
    A table like that of post 1 with the expected results (correct) would be helpful.

    M.

  7. #7
    Board Regular
    Join Date
    Jun 2010
    Posts
    59
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula for Summing A Streak and Restart The Sum after Streak Ends

    ****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">
    A B C D E F G H I J K L M
    1 Monday Tuesday Wednesday Thursday Friday Total Monday Tuesday Wednesday Thursday Friday Total
    2 input Number 5 5 3 -5 -5 -5 4 5 5 -5
    3 positive streak 5 10 13

    13
    4 9 14
    14
    4 negative streak -5 -10 -10 -15

    -5
    -10

    Hi Marcelo

    I placed the formula in each cell to show how it was calculated....What i want to do is find a formula for row 3 and 4 where any streak from monday to friday is calculated and once the streak ends, a new streak is calculated without adding the previous streak. For example, in cell C2, D2, and E2, there is a positive streak of 5,5 and 3 which sums to a streak of 13 points, then the positive streak ends on thursday because in cell F2, there is a -5, then on friday in cell G2, there is another -5, then on the following monday in cell I2, another -5, the negative streak at that point is -15, then this negative streak ends because on tuesday in cell J2, there is a positive 4. wednesday in cell K2, positive 5, thursday in cell L2, positive 5 then the positive streak ends with a streak of 14 points, because on friday in cell M2, there is a negative 5.

    So i want a formula in cells C3:G3, C4:G4, I3:M3, I4:M4 to calculate a streak then stop when the streak ends, sum the new streak that begins without counting the old streak. as you see in the positive streak, there was a streak of 13 from cell C2:E2, but when another positive streak of 14 started in cell J2:L2, it did not add the old positive streak of 13 in cell C2:E2..hopes this makes it clearer....

    A B C D E F G H I J K L M
    1 Monday Tuesday Wednesday Thursday Friday Total Monday Tuesday Wednesday Thursday Friday Total
    2 input Number 5 5 3 -5 -5 -5 4 5 5 -5
    3 positive streak 5
    =C2
    10
    =C2+D2
    13
    =C2+D2+E2

    13
    =C2+D2+E2


    4
    =J2
    9
    =J2+K2
    14
    =J2+K2+L2
    14
    =J2+K2+L2
    4 negative streak -5
    =F2
    -10
    =F2+G2
    -10
    F2+G2
    -15
    =F2+G2+I2




    -5
    =M2
    -10
    =I2+M2

  8. #8
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,085
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Formula for Summing A Streak and Restart The Sum after Streak Ends

    Maybe this...


    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    M
    N
    1
    Monday
    Tuesday
    Wednesday
    Thursday
    Friday
    Total
    Monday
    Tuesday
    Wednesday
    Thursday
    Friday
    Total
    2
    input Number
    5
    5
    3
    -5
    -5
    -5
    4
    5
    5
    -5
    3
    positive streak
    5
    10
    13
    13
    4
    9
    14
    14
    4
    negative streak
    -5
    -10
    -10
    -15
    -5
    -10


    Formula in C3 copied across
    =IF(C1="Total",SUMIF(OFFSET(C2,,-5,,5),">0"),IF(C2>0,SUMIF(INDEX($B2:B2,IFERROR(LOOKUP(2,1/($B2:B2<0),COLUMN($B2:B2)-COLUMN($B2)+1),1)):C2,">0"),""))

    Formula in C4 copied across
    =IF(C1="Total",SUMIF(OFFSET(C2,,-5,,5),"<0"),IF(C2<0,SUMIF(INDEX($B2:B2,IFERROR(LOOKUP(2,1/($B2:B2>0),COLUMN($B2:B2)-COLUMN($B2)+1),1)):C2,"<0"),""))

    Hope this helps

    M.

  9. #9
    Board Regular
    Join Date
    Jun 2010
    Posts
    59
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula for Summing A Streak and Restart The Sum after Streak Ends

    Hi Marcelo,

    Brilliant! you are a true excel master! thank you very much for your help and patience, much appreciated.....

  10. #10
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,085
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Formula for Summing A Streak and Restart The Sum after Streak Ends

    You are welcome. Glad to help.

    M.

Some videos you may like

User Tag List

Tags for this Thread

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
  •