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

1. ## 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,  Reply With Quote

2. ## 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.  Reply With Quote

3. ## 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.....  Reply With Quote

4. ## 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.  Reply With Quote

5. ## 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...  Reply With Quote

6. ## 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.  Reply With Quote

7. ## 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  Reply With Quote

8. ## 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.  Reply With Quote

9. ## 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.....  Reply With Quote

10. ## Re: Formula for Summing A Streak and Restart The Sum after Streak Ends

You are welcome. Glad to help.

M.  Reply With Quote

## User Tag List

cell, formula, positive, streak, sum 