# 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.

#### Hany ali

##### Active Member
how to wait Helping without Upload The File ?!!!!!

#### Jaff

##### New Member
Sorry - here is the file.

#### Attachments

• 16.8 KB Views: 10

#### Peter Bartholomew

##### Well-Known 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]

#### Attachments

• 23.9 KB Views: 14

#### 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?