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

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`?

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))))
``````
• Presumably, there might not always be a "reset" in the column. If that's the case, the error would need to be handled somewhere. Oct 24 at 7:39

# 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

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&" "))