How to sum values in excel until they reach a specified sum?

[Aamir Masood]

Hello everyone,

I have an urgent query. If someone can help me with the formula.

I have an expense sheet with 10,866 rows of data. The total sum of these rows is 2,062. I want a formula which can help me divide this total sum in 200, 100 & 50 range.

I have attached the sheet fyr. Please help.

 

[bines53]

Hi Aamir,

To understand exactly and give you a solution, take only 100 rows, and give an example of a range of 1,2,5 manually, how exactly do you want the solution to look. And what version of Excel do you have?

David

 

[Aamir Masood]

Hello David,

Thanks for your response.

I have expense in B Column and against that I need a formula to sum values of column B until they reach in multiples of 200, 100 & 50. I have manually added the solution for 200 range in Column C.

I have deleted some rows in the test file. Attached is the new file.

Did you get me?

 

[GraH - Guido]

Since you have defined the ranges, then you can use a running sum and lookup the equivalent value.

 

[Aamir Masood]

GraH thank you so much

I don't have the latest version of Excel so its displaying like _xlfn.XLOOKUP. Is there any alternate to this formula or can I add the XLOOKUP as add-in in my older version?

 

[GraH - Guido]

You can use and index match combo.
Without excel for the moment. Index is on the range to return the result from and match is the lookup equivalent.

Next largest match with the MATCH function

To lookup the "next largest" match in a set of values, you can use the MATCH function in approximate match mode, with -1 for match type. In the example shown, the formula in F7 is: =MATCH(F6,length,-1) where "length" is the named range B5:B11, and "cost" is the named range C5:C11.

exceljet.net

 

[bines53]

Hi,

I copied up to line 30, keep copying until the end.

David

 

[petergroft]

Excel Ninja
The main core formula is SUBTOTAL+OFFSET() which generates an accumulative sum of A2:G2. =SUBTOTAL(9,OFFSET(A2,,,,COLUMN($A$1:$G$1)))
The Lookup function based on the criteria returns the closest value (11)
The Match function based on the criteria returns the closest value position (3)

Hope This Helps,
Peter