Finding a formula to compute partial sums for consecutive rows that satisfy a condition-CodePudding

How do I do this is excel. The example is provided below. If we don't sell any products on a specific day I would like to move those hours to the next date.

Excel Table

CodePudding user response：

If you need to compute partial sums of Hours for all the consecutive rows between rows that satisfy the condition that the value Sold is greater than zero, you could do this with an auxiliary column

       A       B       C           D
   ---------------------------------
1 |Hours    Sold    Sums    Solution
2 |  300      30     300         300
3 |   30       0     300           0
4 |    0       0     300           0
5 |   30       0     300           0
6 |  300      50     660         360 
7 |   23       0     660           0
8 |  100      25     783         123

Here Sums is defined by a formula for C2

=IF(B2>0,SUM(A2:$A$2),C1)

You can automatically populate the cells below. This formula puts in a cell a partial sum of Hours up to the current row if Sold is nonzero, otherwise copies the previous partial sum of hours. We need this to subtract this value on the next step.

When you have the column C filled, it is sufficient to put the following formula in D2 and populate the cells below

=IF(ROW(B2)>2,IF(B2>0,C2-C1,0),C2)

This formula handles correctly both D2 that does not have a preceding row with values and the remaining cells in column D.

In fact you could combine the two formulas together and avoid the need to have an auxiliary column. Put the following formula in C2 and spread it down to the rest of the cells in column C

=IF(ROW(B2)>2,IF(B2>0,SUM(A2:$A$2)-SUM(C1:$C$2),0),A2)

to get

       A       B          C
   ---------------------------------
1 |Hours    Sold    Solution
2 |  300      30         300
3 |   30       0           0
4 |    0       0           0
5 |   30       0           0
6 |  300      50         360 
7 |   23       0           0
8 |  100      25         123