Add up to a specified number

[barrai]

hi
I have a list of customers purchases in a1:a20. I have one number as a basis for example in D1 (d1=500)
How to calculate sum of row a1 until which row is 500.

 

[Timbo50]

You say calculate sum of row A1, which are characters not a numbers? Also do you wish to calculate values under the value of D1 or do you want to calculate values up to row 500?

 

[barrai]

i want calculate sum of numbers from a1 to any row when total equal or grater than d1.
for example in up image ,B2+B3+B4+B5 is our answer.

 

[Syed Ali Raza]

Hey barrai,
You need to create a helper column in column C which shows the running total of column B and then use the below function.

=IFERROR(SUMPRODUCT(OFFSET(B1,1,0,MATCH(E1,C2:C21,1)+1)),C2)

Hope this help.

<colgroup><col span="2"><col><col span="2"></colgroup><tbody>
</tbody>

 

[jtakw]

i want calculate sum of numbers from a1 to any row when total equal or grater than d1.
for example in up image ,B2+B3+B4+B5 is our answer.

Hi,

Not sure what you want as a result at All, you need to be more specific.

Take your pick.

Book1
	A	B	C	D	E
1	Customer	Purchase		500
2	a	221	221	b	Customer b in Column A when Sum reaches 500+
3	a	150	371	4	Row 4 in Column B (Not including Header row) when Sum reaches 500+, meaning 340
4	a	95	466	$A$5	Cell address of Customer when Sum reaches 500+
5	b	340	806	$B$5	Cell address of Purchase when Sum reaches 500+
6	c	439	1245
7	c	233	1478
8	c	208	1686
9	c	240	1926
10	c	263	2189
11	d	299	2488
Sheet165

Cell Formulas
Range	Formula
C2	=SUM(B$2:B2)
D2	=INDEX(A2:A11,MATCH(D1,C2:C11,1)+1)
D3	=MATCH(D1,C2:C11,1)+1
D4	=CELL("address",INDEX(A2:A11,MATCH(D1,C2:C11,1)+1))
D5	=CELL("address",INDEX(B2:B11,MATCH(D1,C2:C11,1)+1))

C2 formula copied down.