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

M

MXL

Board Regular
Joined
Jun 15, 2010
Messages
67
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


[TABLE="class: cms_table_grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]K[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]M[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Monday[/TD]
[TD="align: center"]Tuesday[/TD]
[TD="align: center"]Wednesday[/TD]
[TD="align: center"]Thursday[/TD]
[TD="align: center"]Friday[/TD]
[TD="align: center"]Total[/TD]
[TD="align: center"]Monday[/TD]
[TD="align: center"]Tuesday[/TD]
[TD="align: center"]Wednesday[/TD]
[TD="align: center"]Thursday[/TD]
[TD="align: center"]Friday[/TD]
[TD="align: center"]Total[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]input Number[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]-5[/TD]
[TD="align: center"]-5[/TD]
[TD="align: center"][/TD]
[TD="align: center"]-5[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]-5[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]positive streak[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]13[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]13[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]14[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]14[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]negative streak[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]-5[/TD]
[TD="align: center"]-10[/TD]
[TD="align: center"]-10[/TD]
[TD="align: center"]-15[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]-5[/TD]
[TD]
[/TD]
[TD]-10[/TD]
[/TR]
</tbody>[/TABLE]


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

Best Regards,

Vlad
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Sort by date Sort by votes
Don't understand the results you showed in row 4 (negative streak)

Is this what you want?

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]
A​
[/td][td="bgcolor: #DCE6F1"]
B​
[/td][td="bgcolor: #DCE6F1"]
C​
[/td][td="bgcolor: #DCE6F1"]
D​
[/td][td="bgcolor: #DCE6F1"]
E​
[/td][td="bgcolor: #DCE6F1"]
F​
[/td][td="bgcolor: #DCE6F1"]
G​
[/td][td="bgcolor: #DCE6F1"]
H​
[/td][td="bgcolor: #DCE6F1"]
I​
[/td][td="bgcolor: #DCE6F1"]
J​
[/td][td="bgcolor: #DCE6F1"]
K​
[/td][td="bgcolor: #DCE6F1"]
L​
[/td][td="bgcolor: #DCE6F1"]
M​
[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]
1​
[/td][td][/td][td]
Monday​
[/td][td]
Tuesday​
[/td][td]
Wednesday​
[/td][td]
Thursday​
[/td][td]
Friday​
[/td][td]
Total​
[/td][td]
Monday​
[/td][td]
Tuesday​
[/td][td]
Wednesday​
[/td][td]
Thursday​
[/td][td]
Friday​
[/td][td]
Total​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
2​
[/td][td]
input Number​
[/td][td]
5​
[/td][td]
5​
[/td][td]
3​
[/td][td]
-5​
[/td][td]
-5​
[/td][td][/td][td]
-5​
[/td][td]
4​
[/td][td]
5​
[/td][td]
5​
[/td][td]
-5​
[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
3​
[/td][td]
positive streak​
[/td][td]
5​
[/td][td]
10​
[/td][td]
13​
[/td][td][/td][td][/td][td]
13​
[/td][td][/td][td]
4​
[/td][td]
9​
[/td][td]
14​
[/td][td][/td][td]
14​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
4​
[/td][td]
negative streak​
[/td][td][/td][td][/td][td][/td][td]
-5​
[/td][td]
-10​
[/td][td]
-10​
[/td][td]
-5​
[/td][td][/td][td][/td][td][/td][td]
-10​
[/td][td]
-10​
[/td][/tr]
[/table]

M.
 
Upvote 0
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.....
 
Upvote 0
Post 1
I don't understand why K4 (K2 is positive) is equal to -5 and M4 is equal to -10? :confused:

M.
 
Upvote 0
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...
 
Upvote 0
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.
 
Upvote 0
****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">
[TABLE="class: cms_table_cms_table_grid, align: center"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]K[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]M[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Monday[/TD]
[TD="align: center"]Tuesday[/TD]
[TD="align: center"]Wednesday[/TD]
[TD="align: center"]Thursday[/TD]
[TD="align: center"]Friday[/TD]
[TD="align: center"]Total[/TD]
[TD="align: center"]Monday[/TD]
[TD="align: center"]Tuesday[/TD]
[TD="align: center"]Wednesday[/TD]
[TD="align: center"]Thursday[/TD]
[TD="align: center"]Friday[/TD]
[TD="align: center"]Total[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]input Number[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]-5[/TD]
[TD="align: center"]-5[/TD]
[TD="align: center"][/TD]
[TD="align: center"]-5[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]-5[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]positive streak[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]13[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]13[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]14[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]14[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]negative streak[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]-5[/TD]
[TD="align: center"]-10[/TD]
[TD="align: center"]-10[/TD]
[TD="align: center"]-15[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]-5[/TD]
[TD]
[/TD]
[TD]-10[/TD]
[/TR]
</tbody>[/TABLE]
</body>
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....

[TABLE="class: cms_table_cms_table_grid, align: center"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]K[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]M[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Monday[/TD]
[TD="align: center"]Tuesday[/TD]
[TD="align: center"]Wednesday[/TD]
[TD="align: center"]Thursday[/TD]
[TD="align: center"]Friday[/TD]
[TD="align: center"]Total[/TD]
[TD="align: center"]Monday[/TD]
[TD="align: center"]Tuesday[/TD]
[TD="align: center"]Wednesday[/TD]
[TD="align: center"]Thursday[/TD]
[TD="align: center"]Friday[/TD]
[TD="align: center"]Total[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]input Number[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]-5[/TD]
[TD="align: center"]-5[/TD]
[TD="align: center"][/TD]
[TD="align: center"]-5[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]-5[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]positive streak[/TD]
[TD="align: center"]5
=C2[/TD]
[TD="align: center"]10
=C2+D2[/TD]
[TD="align: center"]13
=C2+D2+E2[/TD]
[TD="align: center"][/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]13
=C2+D2+E2

[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]4
=J2[/TD]
[TD="align: center"]9
=J2+K2[/TD]
[TD="align: center"]14
=J2+K2+L2[/TD]
[TD="align: center"][/TD]
[TD="align: center"]14
=J2+K2+L2[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]negative streak[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]-5
=F2[/TD]
[TD="align: center"]-10
=F2+G2[/TD]
[TD="align: center"]-10
F2+G2[/TD]
[TD="align: center"]-15
=F2+G2+I2[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]

[/TD]
[TD] -5
 =M2[/TD]
[TD] -10
 =I2+M2[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Maybe this...


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]
A​
[/td][td="bgcolor: #DCE6F1"]
B​
[/td][td="bgcolor: #DCE6F1"]
C​
[/td][td="bgcolor: #DCE6F1"]
D​
[/td][td="bgcolor: #DCE6F1"]
E​
[/td][td="bgcolor: #DCE6F1"]
F​
[/td][td="bgcolor: #DCE6F1"]
G​
[/td][td="bgcolor: #DCE6F1"]
H​
[/td][td="bgcolor: #DCE6F1"]
I​
[/td][td="bgcolor: #DCE6F1"]
J​
[/td][td="bgcolor: #DCE6F1"]
K​
[/td][td="bgcolor: #DCE6F1"]
L​
[/td][td="bgcolor: #DCE6F1"]
M​
[/td][td="bgcolor: #DCE6F1"]
N​
[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]
1​
[/td][td][/td][td][/td][td]
Monday​
[/td][td]
Tuesday​
[/td][td]
Wednesday​
[/td][td]
Thursday​
[/td][td]
Friday​
[/td][td]
Total​
[/td][td]
Monday​
[/td][td]
Tuesday​
[/td][td]
Wednesday​
[/td][td]
Thursday​
[/td][td]
Friday​
[/td][td]
Total​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
2​
[/td][td][/td][td]
input Number​
[/td][td]
5​
[/td][td]
5​
[/td][td]
3​
[/td][td]
-5​
[/td][td]
-5​
[/td][td][/td][td]
-5​
[/td][td]
4​
[/td][td]
5​
[/td][td]
5​
[/td][td]
-5​
[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
3​
[/td][td][/td][td]
positive streak​
[/td][td]
5​
[/td][td]
10​
[/td][td]
13​
[/td][td][/td][td][/td][td]
13​
[/td][td][/td][td]
4​
[/td][td]
9​
[/td][td]
14​
[/td][td][/td][td]
14​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
4​
[/td][td][/td][td]
negative streak​
[/td][td][/td][td][/td][td][/td][td]
-5​
[/td][td]
-10​
[/td][td]
-10​
[/td][td]
-15​
[/td][td][/td][td][/td][td][/td][td]
-5​
[/td][td]
-10​
[/td][/tr]
[/table]

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.
 
Upvote 0
Hi Marcelo,

Brilliant! you are a true excel master! thank you very much for your help and patience, much appreciated.....
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Summing the results of multiple cells based on other data
Replies
3
Views
106
Fluff
Fluff
D
  • Solved
Is there a quick way to create / fill this list
2 3
Replies
20
Views
422
Eric W
Eric W
K
  • Solved
Conditionally sum fractional cells based on column headings
Replies
5
Views
162
Cubist
Cubist
D
  • Solved
Find a cell in a table, and return the value above
Replies
3
Views
367
Alex Blakenburg
A
W
  • Solved
Formula Error: Error applying formula with VBA even though it works manually
Replies
3
Views
121
6StringJazzer
6StringJazzer

Forum statistics

Threads
1,224,823
Messages
6,181,184
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top