If you’re working with sequences in Sheets, you might need to return the start and end of consecutive values in Google Sheets — whether those values are numbers or dates. This helps in two key ways:

• To spot gaps in your data
• To summarize what’s happening across each uninterrupted stretch

To do this, we’ll use two formulas based on the REDUCE function. One formula returns the start of each sequence, and the other returns the end.

Just make sure your list is sorted in ascending order. These formulas rely on that — otherwise, you’ll get unexpected results.

Sample Data

Here’s an example using a list of sales records by date:

We want to find consecutive dates in column A and list their start and end points. Here’s what that should look like:

1. Formula to Return Start of Consecutive Values in Google Sheets

To get the start of each sequence (in this case, consecutive dates), use this formula in E2:

=TOCOL(
   REDUCE(
      TOCOL(,3), A2:A, 
      LAMBDA(acc, val, 
         VSTACK(acc, IF(ROW(val)=2, val, IF(val=OFFSET(val, -1, 0)+1, ,val)))
      )
   ), 3
)

What this formula does:

• Uses REDUCE to loop through each value in column A
• Checks for gaps — if the current value isn’t one more than the previous, it marks it as the start of a new sequence
• Handles the first value explicitly using ROW(val)=2 (adjust as needed for your data)
• The outer TOCOL(..., 3) removes any blanks or errors, giving you a clean list of start values

2. Formula to Return End of Consecutive Values in Google Sheets

To get the end of each sequence, use this in F2:

=TOCOL(
   REDUCE(
      TOCOL(,3), A2:A, 
      LAMBDA(acc, val, 
         VSTACK(acc, IF(val=OFFSET(val, -1, 0)+1, ,OFFSET(val, -1, 0)))
      )
   ), 3
)

What’s different here:

• It checks where a sequence ends
• Instead of the current value, it returns the previous one — the last value in a run

Bonus: Sum Values Between Start and End

Once you’ve identified the start and end points, you can easily summarize data between them. For example, to get the total quantity (Qty column), enter this SUMIFS formula in cell G2:

=SUMIFS($C$2:$C, $A$2:$A, ">="&E2, $A$2:$A, "<="&F2)

Then drag it down the column to apply it to the rest of your data.

If you prefer array formulas like I do, here’s an alternative using MAP:

=MAP(
   E2:E, F2:F, 
   LAMBDA(start, end, 
      IF(start, SUMIFS(C2:C, A2:A, ">="&start, A2:A, "<="&end),)
   )
)

This loops through each pair of start and end values and returns the total quantity for each range — all at once, no dragging needed.

Tip: If E2:E or F2:F include extra empty rows, the IF(start, ...) wrapper helps skip over them — keeping your output clean and compact.

Conclusion

This approach works great when you want to return the start and end of consecutive values in Google Sheets — whether it’s for dates, numbers, or even IDs.

What I like about this method is that it keeps things simple:

• You get separate formulas for start and end
• You can place them in different columns, even in different locations
• And it plays nicely with summary formulas like SUMIFS

Related Resources

• Convert Dates to Week Ranges in Google Sheets (Array Formula)
• Consecutive Dates to Date Ranges in Google Sheets: The REDUCE Method — Returns both start and end in one go using a single formula. Ideal if you want everything in a compact format. The method in this post is more modular and flexible.
• Count Consecutive Occurrences of Values in Google Sheets
• Highlight N Consecutive Decreases in Numeric Data in Google Sheets
• Highlight Data Spikes (Consecutive Increases) in Google Sheets
• Count Consecutive Workday Absences in Google Sheets