• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

Aamir Masood

New Member
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.
 

Attachments

bines53

Active Member
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

New Member
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?
 

Attachments

Aamir Masood

New Member
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?
 

petergroft

New Member
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
 
Top