stop summing if hits value

[hehehihi2007]

Hi, newbie here,

kindly your help, i have sheet contain production, price & sales
and I want to stop sum when the production is >= sales, and get the last row number, is it possible using the formula?

thx in advance

NB. yellow columns is the result i want.

 

[Cubist]

Why is the result 110,000? Your total sales is 167,500.

 

[kvsrinivasamurthy]

Pl explain in more detailed way.

 

[kvsrinivasamurthy]

For 2021 version
In J2

=LET(a,SUMIF(OFFSET($B$2,0,0,ROW($B$2:$B$7)-ROW($B$1)),">0"),b,BYROW(I2:I5,LAMBDA(x,SMALL(IF(a>x,a,""),1))),c,BYROW(I2:I5,LAMBDA(x,SMALL(IF(a>x,ROW($B$2:$B$7),""),1))),HSTACK(b,c))

For other versions ARRAY formulas
In J2

=SMALL(IF(SUMIF(OFFSET($B$2,0,0,ROW($B$2:$B$7)-ROW($B$1)),">0")>=$I2,SUMIF(OFFSET($B$2,0,0,ROW($B$2:$B$7)-ROW($B$1)),">0"),""),1)

In K2

=SMALL(IF(SUMIF(OFFSET($B$2,0,0,ROW($B$2:$B$7)-ROW($B$1)),">0")>=$I2,ROW($B$2:$B$7),""),1)

Both copied down.
To enter ARRAY formula
Copy and paste the formula in cell
Press F2
Press Ctrl+Shift+Enter together
Excel covers the formula with {}.

	10000​							75000​	110000​	3​
	100000​							12500​	110000​	3​
	100000​							30000​	110000​	3​
	100000​							50000​	110000​	3​
	100000​
	100000​

 

[Cubist]

Option to avoid OFFSET.

Book1
	A	B	C	D	E	F
1	date	prod		sales	prod	row
2	1	10000		75,000	110000	3
3	2	100000		12,500	110000	3
4	3	100000		30,000	110000	3
5	4	100000		50,000	110000	3
6	5	100000
7	6	100000
8	7
Sheet1

Cell Formulas
Range	Formula
A2:A8	A2	=SEQUENCE(7)
E2:E5	E2	=LET(a,TRANSPOSE($B$2:$B$7),b,COLUMNS(a),m,MMULT(a,N(SEQUENCE(b)<=SEQUENCE(,b))),MIN(IF(m>=D2,m)))
F2:F5	F2	=LET(a,TRANSPOSE($B$2:$B$7),b,COLUMNS(a),m,MMULT(a,N(SEQUENCE(b)<=SEQUENCE(,b))),XMATCH(TRUE,D2<=m)+1)
Dynamic array formulas.