• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to sum values until they reach a specified value

Jaff

New Member
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.
 

Jaff

New Member
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

Well-Known Member
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

New Member
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

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