# Formula to calculate the sum of consecutive 0's and -1's

I am hoping someone can help me with a Google Sheets formula to calculate the minimum sum of consecutive `0`s and `-1`s in a column. `0`s and `-1`s are consecutive until interrupted by any other integer.

For example, in the column below:

1. There are two sets of consecutive `0`s and `-1`s separated by a `1` in `A4`.
2. The first set, in `A1:A3`, adds up to `-2`
3. The second set, in `A5:A10`, adds up to `-4`
4. The formula should therefore return `-4`, which is the minimum sum of consecutive `-1`s and `0`s,
``````      A
+----+
1 | -1 |
2 | -1 |
3 |  0 |
4 |  1 |
5 | -1 |
6 |  0 |
7 | -1 |
8 | -1 |
9 |  0 |
10 | -1 |
+----+
``````
• I think you mean minimum (-4 is less than -2) right? Commented Apr 5 at 3:46
• Correct thank you!
– Will
Commented Apr 5 at 6:22

``````=LET(x,TOCOL(A1:A, 1),
-MAX(
INDEX(
LEN(
SPLIT(
JOIN(,
IF(x<-1, 2,
IF(x>0, 2, SUBSTITUTE(ABS(x), 0,)))),
2)))))
``````

As a single line:

``````=LET(x,TOCOL(B1:B,1), -MAX(INDEX(LEN(SPLIT(JOIN(, IF(x<-1, 2, IF(x>0, 2, SUBSTITUTE(ABS(x), 0,)))), 2)))))
``````
1. `x` stores column A (assuming that's where your numbers are) after first removing any blank rows with TOCOL

2. Next the numbers in the array `x` are replaced as follows:

1. if `x<-1` or if `x>0` it's replaced with `2`
2. if `x=0` it is removed
3. if `x=-1` it's replaced with `1`
For example,
```# Original Values
{-1; -1; 0; 1; -1; 0; -1; -1; 0; -1}
# After Replacement
{1; 1; 2; 1; 1; 1; 1}
```
3. JOIN creates a text string from the array of `1`s and `2`s without any delimiter:

```# Array  (from #2)
{1; 1; 2; 1; 1; 1; 1}

# Text String
"1121111"```
4. SPLIT separates the text string into an array using the `2` as the delimiter, resulting in one array member for each original uninterrupted group of `-1`s and `0`s because each `2` represents a number in the original array that was neither a `-1` or a `0`:

```# Text String  (from #3)
"1121111"

# Array
{11, 1111}```
5. LEN counts the characters in each group:

```# Array  (from #4)
{11, 1111}

# After LEN
{2, 4}```
6. The negative value of the maximum length is returned from the array:

```# Array  (from #5)
{2, 4}

# After -MAX
-4

# Equivalent to
SUM({-1; 0; -1; -1; 0; -1})```
• I am humbled by your answer and your explanation on how you reached it. Thank you so much this worked perfectly and helped me learn how to use these functions. Have a wonderful weekend ahead :)
– Will
Commented Apr 5 at 6:19

Here's an alternative simpler answer, if you are interested:

``````=min(scan(0,A1:A10,lambda(acc,arr,ifs(arr=-1,acc-1,arr=0,acc,true,0))))
``````

`scan` the array, subtracting 1 from the accumulator each time a '-1' is seen, leaving the accumulator unchanged every time a '0' is seen, and resetting the accumulator to '0' if any other value is observed. This generates a running sum with local minima at the positions of the final '-1's in the array. Then simply use min on the whole thing to obtain the global minimum.

• Nice approach. Was already thinking the way I did because of my previous answer on SU. Your approach here makes sense. FYI if referencing another answer (as you did here) link to it as there is no guarantee in the future that there will be only one other answer or that they will be beside each other, and it is simply good practice to link to things that are referenced. +1 Commented Apr 5 at 14:25
• FYI I updated my original answer based on the shake you gave me. TY! Commented Apr 5 at 20:37