Count rows in column until sum is reached excluding negatives

Question

I am trying to count the number of rows (in this case days) until the sum of positive values >= the original negative value or more conceptually an initial investment is fully paid-off. In the below example:
For the -70 (1/1/2021) the value would be 8 (1/1/2021 - 1/8/2021) inclusive.
For the next -70 (1/2/2021) the value would be 9 (1/2/2021 - 1/10/2021) inclusive.
For the next -30 (1/6/2021) the value would be 6 (1/2/2021 - 1/11/2021) inclusive.

I have found the following array formula that works for me:

=MATCH(D2,SUBTOTAL(9,OFFSET(B2,,,ROW(B2:B17)-ROW(B2))),1)

However, this does not pass over the other negative values in the column.

Requirement not to use helper columns if possible

Example of what I am trying to achieve:

+-----------+--------+----------------+
| Date      | Amount | Desired Result |
+-----------+--------+----------------+
| 1/1/2021  | -70    | 8              |
+-----------+--------+----------------+
| 1/2/2021  | -70    | 9              |
+-----------+--------+----------------+
| 1/3/2021  | 0      | 0              |
+-----------+--------+----------------+
| 1/4/2021  | 0      | 0              |
+-----------+--------+----------------+
| 1/5/2021  | 0      | 0              |
+-----------+--------+----------------+
| 1/6/2021  | -30    | 6              |
+-----------+--------+----------------+
| 1/7/2021  | 40     | 0              |
+-----------+--------+----------------+
| 1/8/2021  | 30     | 0              |
+-----------+--------+----------------+
| 1/9/2021  | 0      | 0              |
+-----------+--------+----------------+
| 1/10/2021 | 70     | 0              |
+-----------+--------+----------------+
| 1/11/2021 | 30     | 0              |
+-----------+--------+----------------+

Any help would be appreciated -

user_3894917 · Answer

Well I've come up with a formula that agrees with your figures. It may be more complicated than it needs to be because although your test data always works out so that the exact amount is paid off (exact match), I've added the case where an excess amount is paid (inexact match).

The principle is to develop a triangular matrix which uses mmult to select just one row of data, then two rows etc.

=IF(B2<0,IFERROR(MATCH(-SUMIF(B2:B$2,"<0"),MMULT(N(ROW(A$2:A$12)>=TRANSPOSE(ROW(A$2:A$12))),IF(B$2:B$12>0,B$2:B$12,0)),0),
MATCH(-SUMIF(B2:B$2,"<0"),MMULT(N(ROW(A$2:A$12)>=TRANSPOSE(ROW(A$2:A$12))),IF(B$2:B$12>0,B$2:B$12,0)),1)+1)-ROW()+2,0)

Count rows in column until sum is reached excluding negatives

1 Answers