1

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?

3 Answers 3

0

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))))
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.
    – Blindspots
    Commented Oct 24, 2023 at 7:39
0

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_CONSTRAINIFNALETMATCHROWROWSSORTSUM

0

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 you have read our privacy policy.

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