Sum A Streak and Restart Sum after Streak Ends Question

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 I2, 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


ABCDEFGHIJKLM
1MondayTuesdayWednesdayThursdayFridayTotalMondayTuesdayWednesdayThursdayFridayTotal
2input Number553-5-5-5455-5
3positive streak51013

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

-5
-10

<tbody>
</tbody>

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

Best Regards,

Vlad
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Then there is a negative streak that ends in cell I2, the positive streak resumes in cell I3
Looks to me like that negative streak actually ends in H2, not I2.
And the positive streak resumes in cell I2, not I3.

The table you have posted - does that contain all the results as they should be ?
If NOT, please repost the same table, but this time showing all the results with the values as you would like to see them.

Also, is it necessary to have the weekly totals (in col G, M etc) ?
This task might be easier if you can delete them.
 
Upvote 0
Hi Gerald,

Yes that is the entire results, yes the negative streak does end in H2, the columns total in cell G3:G4, M3:M4 can be deleted yes.....but if there is a formula that can get around the totals, it would be convenient, but if not, its okay you can create a formula with cell G3:G4, M3:M4 deleted.

Thank you for taking the time to read this, much appreciated and look forward to hearing from you.
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,109
Members
448,548
Latest member
harryls

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