# How to sum an answer tracing upwards until it hits an positive value?

Thx for coming in! I need a formula to trace and sum the value in a column, upwards, until it hit an positive value.

For the following table, A will have the answers, and B will be the column where I need the formula.

I want a non-array formula as manual editing could be needed.

A B
1 0
0 0
-1 -1
-2 -3
-1 -4
0 -4
-4 -8
1 0

As you can see, I need an non-array formula for each cell in column B. In column A, where the answers appeared, if a negative value starts to show up, and finally a positive number shows up, I see it as a cycle is completed.

In column B it sums up all the negative value in a cycle upwards until it hits an positive value. Of course it also need to skip the value 0 and keep tracing upwards if that happened.

What formula could I use for column B?

Thx again!

Use `ifs()` and `offset()`, like this:

``````=ifs(
A1 > 0, 0,
row(B1) = 1, A1,
true, offset(B1, -1, 0) + A1
)
``````

See ifs() and offset().

• it works amazingly, but there is a slight issue. Sometimes in A column the data will be missing, and the formula need to skip the empty cell and keep tracing upward. It is possible? Commented May 13 at 8:25
• The formula will treat truly blank values the same as zeros. To also treat zero-length text strings `""` the same as zeros, replace the first instance of `A1` with `value(A1)`. Please do not present new requirements after you have received an answer. Ask only one question per post. Post a new question instead. Commented May 13 at 8:40
• understood, I will post a new question even it's similar but with new requirements next time. Thx 4 the help Commented May 13 at 9:04