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