Excel Running total reaching a threshold and choosing a corresponding date

[DaveyMac]

Hi,

I have a row of Dates in a Quarter (around 90 separate days) and below each date is a figure for the number of products sent that day. i want to cumulatively add (running total) the number of Products sent each day from Day 1 until they reach a cumulative threshold of 3 and then copy the date that this threshold is met to a field at the end of the row.

I have tried all sorts of formulas and used Chat GPT but no luck.

It doesn't matter what the value in the cell is, when it reaches the cumulative value count of 3.

[#2]

@DaveyMac I didn't understand it completely. But, Is this what you want ? (Yellow highlighted part)

[#3]

@DaveyMac 

This may or may not be what you require!  It uses 365 and I do not 'do' backward compatibility.

= LET(
    cumulative, SCAN(0, sent, SUM),
    XLOOKUP(3, cumulative, VSTACK(date, cumulative), "not reached", 1)
  )

[#4]

Despite having said I do not do backward compatibility, I had better remove the eta-reduced Lambda function for now!

= LET(
    SUMλ, LAMBDA(x,y, SUM(x,y)),
    cumulative, SCAN(0, sent, SUMλ),
    XLOOKUP(3, cumulative, VSTACK(date, cumulative), "not reached", 1)
  )