• 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


  • 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


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.