Add Numbers Until Value Reached

Question

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).
As A1 is 10, cells from A6:A5 should be added - Result:10
If A1 is 6 then cell A6 should be returned- Result:6
If A1 is 16 then cells from A6:A3 should be added - Result:19

Is this possible without VBA? Can i get count of number of cells in return along with sum?

Answer 1

I've added some progression SUM operation with ROW and OFFSET for the following. Note that I have modified and added to your sample data for more thorough results.

      

The SUM formula in C2 is =SUM(OFFSET($A$2,0,0,MAX(INDEX((SUBTOTAL(9,OFFSET($A$2, 0,0,ROW(1:99),1))<$A$1)*ROW(1:99),,))+1,1)) anf the COUNT is derived in D2 with =MAX(INDEX((SUBTOTAL(9,OFFSET($A$2, 0,0,ROW(1:99),1))<$A$1)*ROW(1:99),,))+1. TBH, I didn't experiment much with zeroes in the data as I was unsure whether you would want to count them in the progression or not.

Answer 2

You can use the INDIRECT() function.

=SUM(INDIRECT("A6:A"&ROUNDDOWN(A1/2,0)))

for the count use

=COUNT(INDIRECT("A6:A"&ROUNDDOWN(A1/2,0)))

Answer 3

It's hard to hit a moving target but for your revised parameters try the following.

=SUM(OFFSET($A$6,0-MAX(INDEX((SUBTOTAL(9,OFFSET($A$6,1-ROW(1:5),0,ROW(1:5),1))<$A$1)*ROW(1:5),,)),0,MAX(INDEX((SUBTOTAL(9,OFFSET($A$6,1-ROW(1:5),0,ROW(1:5),1))<$A$1)*ROW(1:5),,))+1,1))  

=MAX(INDEX((SUBTOTAL(9,OFFSET($A$6,1-ROW(1:5),0,ROW(1:5),1))<$A$1)*ROW(1:5),,))+1

While Excel expects to calculate in a 'down-and-to-the-right' progression, the OFFSET() function will accept parameters to both relocate the starting point and reshape the height and width of the range of cells being summed. Generally, working 'down-and-to-the-right' will allow to leave some breathing room for expansion but you will have to be very careful that you do not attempt to move upwards past row 1 (#REF! error).

OFFSET function

The formula could be simplified if there was any guarantee that nothing of numerical value was ever going to be below A6 but that point has not been addressed so my formulas halt the sum operation at A6.