Excel: Return the Date a Running Total Reaches a Threshold
Question details
Calculate cumulative quantities across a row and return the first corresponding date when the running total reaches or exceeds a specified threshold.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Dates are arranged horizontally, with daily product quantities in the row below, and the user needs the first date at which the accumulated quantity reaches a target such as 3.
- Observed behavior
- The desired result is the first date associated with a cumulative total that is equal to or greater than the threshold.
Make sure the date range and quantity range contain the same number of columns and are aligned vertically. Replace the example ranges and threshold value with the cells used in your worksheet.
Use a running-total helper row and INDEX MATCH
Use this method when you want a clear, compatible formula setup for horizontal dates and daily quantities.
A helper row calculates the cumulative quantity for each date. INDEX and MATCH then locate the first cumulative value that reaches or exceeds the target.
If dates are in B1:Z1 and daily quantities are in B2:Z2, click B3 and enter =SUM($B$2:B2). Drag the fill handle across to Z3 to calculate the running total for every date.
Click the cell where you want the result and enter =INDEX(B1:Z1,MATCH(TRUE,B3:Z3>=3,0)). Replace 3 with your target value or a cell reference such as A1.
In Microsoft 365 or current WPS Spreadsheets versions, press Enter. If your spreadsheet version does not evaluate the TRUE comparison as an array, confirm the formula with Ctrl+Shift+Enter.
Use SCAN and XLOOKUP in Microsoft 365
Use this single-cell approach when your Excel version supports the SCAN, LET, and XLOOKUP functions.
Find the First Threshold Date in WPS Spreadsheets
WPS Spreadsheets can calculate a cumulative row and use INDEX with MATCH to return the first date where daily quantities reach your target. The layout is easy to audit because each date has a visible running-total result.
- 1. Open the worksheet in WPS Spreadsheets: Open your .xlsx or .xls file and verify that the dates are in one row and the matching quantities are directly beneath them.
- 2. Build the cumulative row: Select the cell below the first quantity, enter =SUM($B$2:B2), and drag the fill handle to the right across all quantity columns.
- 3. Return the threshold date: Select an empty result cell and enter =INDEX(B1:Z1,MATCH(TRUE,B3:Z3>=3,0)), updating the ranges and threshold for your sheet.
- 4. Confirm and format the result: Press Enter, then use Home > Number Format > Date if the returned value is not displayed as a date.
Frequently Asked Questions
How can I use a threshold stored in a cell instead of typing 3 in the formula?
Replace 3 with the threshold cell reference. For example, if the target is in A1, use =INDEX(B1:Z1,MATCH(TRUE,B3:Z3>=A1,0)).
What happens if the cumulative total never reaches the target?
MATCH returns an error when no running total meets the threshold. To show a message instead, use =IFERROR(INDEX(B1:Z1,MATCH(TRUE,B3:Z3>=3,0)),"not reached").
Why does the formula return the first qualifying date rather than the last one?
MATCH(TRUE,B3:Z3>=3,0) searches from left to right for the first TRUE result, which is the first running total equal to or greater than the target.
Can I use this method when dates are listed vertically?
Yes. Put the running-total formula beside the quantities, then use INDEX on the vertical date range and MATCH on the vertical cumulative-total range.
