How would I SUM column values while excluding any that precede the text string "reset"?

Question

I have a spreadsheet where the hours of wear for pieces of equipment are logged:

• Data is logged in new rows as additional wear-hours are put on the equipment.
• From time to time, equipment is replaced, and a text entry "reset" is added to the column and logging of the replacement continues below that entry.
• This may be repeated several times, and hours logged for equipment that was replaced is never removed.

How can I add the total hours logged in a column while excluding any entries that precede the very last instance of "reset"?

Sample Data

	A	B
1	Current	21
2	Starting	500
3	Day 1	10
4	Day 2	RESET
5	Day 3	15
6	Day 4	6

What formula would I put in B1 that would add B5:B since B2:B3 is followed by a "RESET" in B4?

Answer 1

Given a column of values/resets in B2 downwards, the following in B1 will calculate the sum of the values after the last instance of 'reset':

=let(
array,tocol(B2:B,1),
lastreset,xmatch("reset",array,0,-1),
sum(chooserows(array,sequence(rows(array)-lastreset,1,lastreset+1))))

Answer 2

Formula

=LET(arr,SORT(B2:B,ROW(B2:B),0),
  SUM(ARRAY_CONSTRAIN(arr,
    IFNA(MATCH("reset",arr,0),ROWS(arr)),1)))

Explanation

1. arr stores column B in reverse order.
2. ARRAY_CONSTRAIN reduces the height of arr to just enough rows to include the first (now that it's reversed) instance of "reset", or, if "reset" is not found, IFNA returns the total number of rows in arr
3. The resulting array is passed to SUM

NOTE: SUM ignores text and blanks so I didn't bother reducing the array an extra row to eliminate "reset", nor did I worry about the empty rows below the data.

---

Functions

ARRAY_CONSTRAIN IFNA LET MATCH ROW ROWS SORT SUM

Answer 3

You can learn the QUERY formula below:

=SUM(QUERY(B1:B,"Select B Where B is not null Offset "&MATCH("reset",B1:B,0)-1&" "))