Sum until target, give a counter/ trigger once reached target, keep summing

New Contributor


I've got demand data for 6 months, I would like to sum the data until the capacity of truck say 1000kg is reached. Once the column reaches the 1000 mark I would like to get a trigger/ break that puts 1 in the adjacent column. Since its 6 month data that varies  I need this automated for every 1000kg it passes in demand column.


4 Replies


This is a 365 solution that is probably total overkill for your requirement!

    cumulative, SCAN(0,weight,LAMBDA(acc,w,IF(acc+w<=1000,acc+w,w))),
    newLoad,    IF(cumulative=weight,1,0),
    IF(VSTACK(DROP(newLoad,1),1), cumulative,"")


Very tricky formula. Like it.


For older versions: with data in A2 and down, enter the following formula in B2, then fill down:



best response confirmed by smoiz (New Contributor)


Used the following, worked for me since I had  trigger column running parallel to data. The input reference is to the capacity value at which I want a trigger.




Thanks for all the help, appreciate it.