Question: Suppose you have an ordered, indexed list of positive values. These positive values are interrupted by 0 values. I want to determine if a consecutive sub-array exists which is not interrupted by 0 values and whose sum exceeds a certain threshold.

Simple example:

```
Index, Value
0   0
1   0
2   3
3   4
4   2
5   6
6   0
7   0
8   0
9   2
10  3
11  0

```

In the above example, the largest consecutive sub-array not interrupted by 0 is from index `2` to index `5` inclusive, and the sum of this sub-array is `15`.

Thus, for the following thresholds `20`, `10` and `4`, the results should be `FALSE`, `TRUE` and `TRUE` respectively.

Note I don't necessarily have to find the largest sub-array, I only have to know if any uninterrupted sub-array sum exceeds the defined threshold.

I suspect this problem is a variation of Kadane's algorithm, but I can't quite figure out how to adjust it.

The added complication is that I have to perform this analysis in Excel or Google Sheets, and I cannot use scripts to do it - only inbuilt formulas.

I'm not sure if this can even be done, but I would be grateful for any input.

Answer: 
With data in columns **A** and **B**, insure column **B** end with a 0. Then in **C2** enter:

=IF(AND(B3=0,B2<>0),SUM(B$1:$B2)-MAX($C$1:C1),"")

and copy downwards:

[![enter image description here](https://i.stack.imgur.com/mqfIc.png)](https://i.stack.imgur.com/mqfIc.png)

Column **C** lists the sums of consecutive non-zeros. In another cell enter something like:

```
=MAX(C:C)>19

```

where ***19*** is the criteria value.

You can avoid the "helper" column by using a VBA UDF.

**EDIT#1:**

Use this instead:

```
=IF(AND(B3=0,B2<>0),SUM(B$1:$B2)-SUM($C$1:C1),"")

```
Thanks to @Tom Sharpe and @Gary's Student for answering the question.

While I admittedly did not specify this in the question, I would prefer to achieve the solution without a helper column because I have to do this operation on 30+ successive columns. I just didn't think it was possible in Excel.

**[Full credit goes to user XOR LX on the Excelforum for coming up with this solution](https://www.excelforum.com/excel-formulas-and-functions/1313589-finding-uninterrupted-sub-arrays-in-excel-kadanes-algorithm-variation.html)**. It has blown my mind and took me the better part of an hour to wrap my head around, but it is certainly very creative. There is no way I could have come up with it myself. Re-posting it here for the benefit of everyone who is looking into this.

Copy and paste the table from my initial question into an empty Excel sheet such that the headers appear in `(A1:B1)` and the values appear in `(A2:B13)`.

Then enter this formula as an array formula (ctrl+shift+enter), which gives the max of the sums of all the uninterrupted sub-arrays:

```
=MAX(SUBTOTAL(9,OFFSET(B2,A2:A14,,-FREQUENCY(IF(B2:B13,A2:A13),IF(B3:B14=0,A2:A13,0))-1)))

```

Note the deliberate offset to include one additional row below the end of the dataset.