How to sum values until they reach a specified value

[Jaff]

I want to have a threshold value in one cell (A1) and take it as a reference for adding cells.

Suppose I have

A1 - 10
A2 - 4
A3 - 2
A4 - 3
A5 - 4
A6 - 6

I want to add cells based on A1(Threshold).

If A1 is 10, it would add A2:A5, sum = 13
If A1 is 9, it would add A2:A4, sum = 9

The SUM formula is
=SUM(OFFSET($A$2,0,0,MAX(INDEX((SUBTOTAL(9,OFFSET($A$2, 0,0,ROW(1:5),1))

But I have a problem when the sum cannot be reached in interval set in the ROW formula.

In the exsample if you set A1 to 14, you will get 19. So the formula keeps adding even when max ROW is reached.

How can I stop this? I want it to stop at 13 or #I/T.

 

[Hany ali]

how to wait Helping without Upload The File ?!!!!!

 

[Jaff]

Sorry - here is the file.

 

[Peter Bartholomew]

Your formula seems to work OK to me.
I tried both your SUBTOTAL/OFFSET solution and one based on XLOOKUP/SUMIFS with identical results.
[The file is Office 365]

 

[Jaff]

Your formula seems to work OK to me.
I tried both your SUBTOTAL/OFFSET solution and one based on XLOOKUP/SUMIFS with identical results.
[The file is Office 365]

View attachment 65419

Thanks Peter.

But it still dosen't work if you forexample put a 5 in B10 and set the treshold to 20 you get 24. So it dosen't stop even when the max row is reached. This is want I don't understand, how can it keep going?

 

[Peter Bartholomew]

I think that is the way in which all the formulas are set up. The XLOOKUP I suspect I introduced is set to return a match or the next larger, having a final parameter of 1 which would need to change to -1. The other formula tests for a final row that is strictly less than the threshold so that it can add 1 to final row. To move to equal or less that test would need to be changed to "<=" and the +1 removed from "1 + finalRow".

 

[Jaff]

I think that is the way in which all the formulas are set up. The XLOOKUP I suspect I introduced is set to return a match or the next larger, having a final parameter of 1 which would need to change to -1. The other formula tests for a final row that is strictly less than the threshold so that it can add 1 to final row. To move to equal or less that test would need to be changed to "<=" and the +1 removed from "1 + finalRow".

Perfect. I think its working now.

Thanks for the help!

 

[Kayt]

@Peter Bartholomew I have a similar question but will need to include a SUMIF formula. Can you help?

How to sum values just over a quantity we want

I want to find a formula to sum up a column equal or over a specific value. For example: I have a list of products with quantities and prices (Row 8 to Row 40) For Product A, I want to sum up enough Quantities in the list that would equal 30 or 1 item more than 30 quantities. - Product A...

chandoo.org