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

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?

3 Answers 3


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':

  • Presumably, there might not always be a "reset" in the column. If that's the case, the error would need to be handled somewhere.
    – Blindspots
    Oct 24 at 7:39




  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.




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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.