Sum to a max value based on ranking

[Sjloutz]

Hi - this is my first post. I’ve generally been successful in searching for an answer to my questions but I haven’t been able to find anything that seems to cover my need.

I have a worksheet that lists each line as a percentage of a total volume. In another column, I have a list of values. I need a column to assign these values by the descending rank of the other column until their sum meets a specific number.

I use sumifs a lot but am having a hard time applying it with the rank criteria. I’m actually not sure if it’s possible but I feel like I’m probably over thinking it.

thanks!
example:
Total sum =12
Rank Volume Result
6 4
3. 2 2
4. 3
2. 6 6
5. 4
1 4 4

 

[Eric W]

Welcome to the MrExcel forum!

How about:

Book1
	A	B	C	D	E
1	Rank	Volume	Result		Total sum
2	6	4			12
3	3	2	2
4	4	3
5	2	6	6
6	5	4
7	1	4	4
Sheet4

Cell Formulas
Range	Formula
C2:C7	C2	=IF(SUMIF(A:A,"<="&A2,B:B)<=E$2,B2,"")

 

[Sjloutz]

THIS IS PERFECT! Thank you so much. I completely overlooked using an &.

 

[Eric W]

Happy to help!

 

[mtryon]

Welcome to the MrExcel forum!

How about:

Book1
	A	B	C	D	E
1	Rank	Volume	Result		Total sum
2	6	4			12
3	3	2	2
4	4	3
5	2	6	6
6	5	4
7	1	4	4
Sheet4

Cell Formulas
Range	Formula
C2:C7	C2	=IF(SUMIF(A:A,"<="&A2,B:B)<=E$2,B2,"")

I want to do something almost identical to this, except I would want to calculate exactly to cell E2. Currently, it will sum up to the last full value of column B without going above E2, but I would want to use a partial value from column B.

 

[Eric W]

How about:

Book1
	A	B	C	D	E
1	Rank	Volume	Result		Total sum
2	6	4			17
3	3	2	2
4	4	3	3
5	2	6	6
6	5	4	2
7	1	4	4
Sheet8

Cell Formulas
Range	Formula
C2:C7	C2	=IFERROR(1/(1/MEDIAN(0,B2,$E$2-SUMIF(A:A,"<"&A2,B:B))),"")

 

[mtryon]

How about:

Book1
	A	B	C	D	E
1	Rank	Volume	Result		Total sum
2	6	4			17
3	3	2	2
4	4	3	3
5	2	6	6
6	5	4	2
7	1	4	4
Sheet8

Cell Formulas
Range	Formula
C2:C7	C2	=IFERROR(1/(1/MEDIAN(0,B2,$E$2-SUMIF(A:A,"<"&A2,B:B))),"")

This worked absolutely perfectly! I really appreciate the help!
I had my doubts that you'd answer this quickly to such an old thread, but you really came through. I actually made my own thread to ask my variation of this. You can pretty much copy your answer over to it if you're interested in getting another solution under your belt.

 

[Eric W]

Well, I am a bit of an old-timer, but you did get a bit lucky that I happened to notice it so soon. Glad I could help!

I'll go leave a comment in the other thread just so other people won't spend time working on it. Opening up a new thread is the recommended method of asking a new question, just in case someone doesn't happen to notice a new message on a very old thread.