Excel - Dynamic SUM formula [0,1]

I trying to make a dynamic Excel formula that sums values in a row when the values in another row are 1 followed by 0. If after the last 0, there is a 1, the sum again until you reach another 1.

As you can see in the picture, the first value in the F6 must be 323 (202+47+74), then a 1 appears but no 0 below, so the value remains the same (1997). Then another 1 followed by two 0 that makes 9 (4+3+2), and so on.

Here is another alternative,

• Formula used in cell F2

=LET(
a,B2:B13,
b,C2:C13,
c,SCAN(0,a,LAMBDA(x,y,x+y)),
d,UNIQUE(c),
IF(a=0,"",INDEX(MMULT(N(TOROW(c)=d),b),XMATCH(c,d))))

Notes: Refer below what each variable does:

• Firstly, we are taking both the ranges i.e. B2:B13 & C2:C13 and defining them as a & b respectively.

• Next, we are using SCAN() function to return an array, with an initial value of 0 it iterates over each value in the sequence of a which adds the current x to the accumulated value of y from the previous iterations using the LAMBDA(). The return is assigned as c.

SCAN(0,a,LAMBDA(x,y,x+y))

• Now, we are using UNIQUE() function to remove any duplicates from c

=LET(
a,B2:B13,
b,C2:C13,
c,SCAN(0,a,LAMBDA(x,y,x+y)),
d,UNIQUE(c),
d)

• Next, we are performing a matrix multiplication using the MMULT().

N(TOROW(c)=d)

The above compares each value in the array c with each in the array d and returns a BOOLEAN array of the same size where if its equal then TRUE else FALSE. Then the MMULT() performs the matrix multiplication which results in same numbers of rows and columns as in b where it sums the product of each value of BOOLEAN with corresponding b.

MMULT(N(TOROW(c)=d),b)

• Finally we are using an IF() and INDEX() wtih XMATCH() to return the required output as desired.

==> The XMATCH() function is used to find the position of each value of c within the array d which is used within the INDEX() to return the corresponding values of the above matrix multiplication. Lastly wrapping it within the IF() function which checks if any value in the array a is equal to 0 then it returns empty else it gives the value returned using INDEX() & XMATCH().

Ofcourse, you don't have to fill down, as it will spill dynamically.

This could also be done without helpers/lambda using MMULT:

=LET(a, B3:B14,
     b, C3:C14,
     x, SEQUENCE(ROWS(a)+1),
     y, DROP(MMULT(--(TOROW(x)<=x),--VSTACK(a,1)),-1),
IF(a,MMULT(--(TOROW(y)=y),b),""))

It first stores ranges a and b for easy reference in the formula.

Then x is a counter of the number of rows of range a +1 *.
x is used in y to check if x is equal to or greater than other values in the range multiplied by the qty of 1 found in these rows from range a*.

* An extra 1 is added to the range a in order to simulate a final 1 in the range a up to where we want to sum the final values evetually.

So y is a counter that starts at the first found 1 and adds up +1 if value in a equals 1 again.
After having calculated this the helper row at the end is no longer needed, since it calculated up to where it should sum. Therefore we remove (drop) the last row from y for size compatibility to the range to sum (b).

Finally it checks if a is 1. If it does it sums the rows of b where the rows in y equal the number in that row of y.

Sum Up Groups

=LET(o,B3:B14,v,C3:C14,c,1,n,"",
    r,ROWS(o),rs,SEQUENCE(r),iss,FILTER(rs,o=c,""),
    ies,IF(ROWS(iss)=1,r,VSTACK(DROP(iss-1,1),r)),
MAP(rs,LAMBDA(mr,
    IF(INDEX(o,mr)<>c,n,LET(
        ri,XMATCH(mr,iss),is,INDEX(iss,ri,1),
        ie,INDEX(ies,ri,1),sr,SEQUENCE(ie-is+1,,is),
    SUM(INDEX(v,sr)))))))

Here another array solution approach, i.e. it spills the entire result all at once (formula 1):

=LET(A,A1:A12, B,B1:B12, n,ROWS(A), seq,SEQUENCE(n),
 MAP(seq, LAMBDA(s,IF(INDEX(A,s)=1,LET(end, @FILTER(seq, (seq>s) * (A=1),n+1),
 SUM(FILTER(B, (seq>=s) * (seq<end)))),""))))

or you can use this alternative (formula 2). The previous formula has a little overhead calculation, because we need to get just the first element of the first FILTER call, but we get the entire FILTER output. The following formula avoids that.

=LET(A,A1:A12, B,B1:B12, n,ROWS(A), seq,SEQUENCE(n), idx,IF(A=1,seq,0),
 MAP(idx, LAMBDA(x, IF(x=0,"", SUM(FILTER(B, (seq>=x) 
  * (seq<XLOOKUP(x+1,idx,idx,n+1,1))))))))

Here is the output for formula 1:

In formula 1 it iterates via MAP over all index positions of the input (seq). On each iteration (s) it checks for the given index s if the column A values (A) is equal to 1 via INDEX. If that is the case (otherwise it returns an empty string), it finds the index position (end) of the next 1 value for index position greater than the current iteration value (s) via FILTER function. Since we are only interested in the first value (index position of the next 1 value) of the FILTER output we use Implicit intersection operator: @. If the condition doesn't match, then we use the third input argument of FILTER to return n+1, where n is the number of rows of the input data.

Since end represents the index position of the next 1 value or the number of rows plus one in case no more 1 value were found, now we can use FILTER again to select B column values (B)
from s to end-1 index positions and sum it.

In formula 2 it identifies first the index positions where A is equal to 1 (idx), otherwise returns 0. By definition non zero values of idx are in ascending order. To identify the end of the interval (position of the following 1 value in A), it uses XLOOKUP with approximate search (1-equal or greater) to look for the next element of x, i.e. x+1. It returns the position of the next 1 value in A, otherwise the n+1. Therefore the range of the B to sum is filtered for the index positions seq between x and the output of XLOOKUP.

Performance Analysis

Here a summary based on different scenarios to measure the performance of different answers provided to this question. I am considering the following scenarios:

1. A: worse case scenario, 1 in the first row and the rest with zeros
2. B: Random set for column A, with significant more 0s than 1s
3. C: Random set for column A, with uniform distribution for 1s and 0s.

In all cases I am considering an input of 7000 rows.

To generate a non-uniform [0,1]-distribution I use the following:

=LET(rnd, RANDARRAY(10000,1,1,10,1),IF(rnd=1,1,0))

I am considering the following solutions:

1. Provided by: @VBasic2008 with the correction indicated in the comment section of his answer.
2. Provided by: @MayukhBhattacharya. This solution uses MMULT, but it works for more than 7500 rows.
3. Provided by: @P.b (initial approach, not the approach provided in the comment section, which doesn't provide a correct result). Worth to notice that this solution stops working around 7500 rows.
4. Provided by @DavidLeal formula 2 using XLOOKUP. The formula 1 was inefficient, so it was not considered in the analysis.

Here are the results for Excel Desktop:

I would say the solution provided by @MayukhBhattacharya is the best one in all scenarios tested, then the solution provided by @VBasic2008, it works pretty well, but it fails for the worse case scenario (A) taking significant time.

It brought my attention that running the same test under Excel for Web (free version). I don't get the same results. Again MayukhBhattacharya and VBasic2008 are the best solutions, but this time the solution provided by VBasic2008 performs better:

I tested also @JvdV, provided in the comment section of P.b solution, which is at the end a variation of @MayukhBhattacharya approach. @JvdV is more efficient than @P.b solution, but worse than @MayukhBhattacharya solution.

Worth to mention that @MayukhBhattacharya solution can be optimized because the input argument lookup_array from XMATCH is sorted in ascending order, so we can use a binary search in XMATCH using the input argument search_mode=2. Which provides an improvement around 9%. The same optimization applies to @VBasic2008 solution, since it uses XMATCH with lookup_array in ascending order.

Here is the link to the Excel file used for doing the performance analysis. The summary of the result is on the first tab. Be aware the file has the following configuration: Formulas -> Calculation Options->Manual, to avoid any specific calculation interfere other results. It uses volatile Excel functions (RANDARRAY, NOW) so it avoids automatic recalculation.

It would be interesting to verify the results by others.

Conclusion

Using the idea of finding start/end for each group, used by @VBasic2008 and by @DavidLeal. Using INDEX performs better than FILTER. For example @DavidLeal solution modified to remove FILTER as follows has a similar performance as @VBasic2008 solution, but not better:

= LET(A,A1:A12, B,B1:B12, n,ROWS(A), seq,SEQUENCE(n), idx, 
  IF(A=1,seq,0), MAP(idx, LAMBDA(x, IF(x=0,"",
  LET(xe, XLOOKUP(x+1,idx,idx,n+1,1)-1,SUM(INDEX(B, SEQUENCE(xe-x+1,,x))))))))

@VBasic2008 solution has a simple way to find start/end of the intervals, compared to the previous formula, therefore the performance is better.

Solutions using MMULT to identify each group, work better when the calculation involves a reduced portion such as in @MayukhBhattacharya solution, compared to @P.b solution. Which is also good to avoid any possible Excel limits.

Both approaches are good strategy, taking into account previous considerations.

Still an open question, why some scenarios work better depending on Excel platform (Desktop, Web) used. Probably internally the functions are not implemented in the same way, or different version for the same functions used.

You can do this easily with a (hidden) extra column. Keep the running total in the hidden column, and display that running total only when the [0, 1] column is 1.

Please note, in future posts provide the data as a table we can copy, not as a screenshot.

Matching the rows in your data, in row 3 in the hidden column (F is the hidden column here):

=IF(B3=1, IF(B4 = 1, C3, D4+C3), IF(B4 = 1, C3, C3+D4))

Copy that down column F.

In row 3 in the display column (G here):

=IF(B3 = 1, F3, "")

Of course, copy that down column G.

A simpler answer than many others are suggesting is as follows (inspired by @richardcook)
Have a helper column for when there are multiple zeroes in column B:

The formula for the helper column is =IF(AND(C4=0,C5=0),D4+F5,D4) and the formula for the final answer is =IFS(C4=0,,AND(C4=1,C5=1),D4,AND(C4=1,C5=0),D4+F5)

What the block of ifs is doing is similar to RichardCook's answer where the formula checks to see if the initial column is 1 or 0 and if the column is 1 checks to see if there are multiple zeroes. If there are multiple zeroes it uses the helper column to find the total.