Sum values, but not exceeding unit count

[Slimm]

Maybe its just Friday and my brain is off for the 3 day weekend, but I'm totally struggling with this issue.

I need to sum the values of the units sold, but I can only sum the value up to the total units replaced. Dropping off the lowest values.

So I need to rank the highest unit value and multiply the units sold times that value up to the total units replaced.

rank	value	units sold	unit value		units replaced
3	400	2	200		4
2	1750	5	350
1	1200	1	1200
Total	3350		1750

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

 

[LxQ]

Maybe an example would help because it could get confusing...

So, if I understand it correctly, you have two units sold on the first line, but 4 replaced.. so you can count 2 there..
next line you have 5 sold, but none replaced, so you can't count any...

then it would be "unit value" * MIN("units sold", "units replaced")

 

[Slimm]

In the example above there are a total of 8 units sold, but only 4 units existed prior to the sale. So only 4 qualify for the replacement "value".

Rank the unit values, take the largest up to the units being replaced (4)

Rank 1 - 1 unit / 1200
Rank 2 - 3 units / 350 * 3

The Total would be 2,250

Rank 2 - 2 units do not qualify
Rank 3 - 2 units do not qualify

Thank you for your help

 

[LxQ]

Got it... how many of these ranks can you have?

 

[LxQ]

Can there be duplicates, same values, things like that?

This Friday thing must've gotten all of us... if you only have 3 ranks.. let's say your data is in A2:d4 with the "4" for unit replaced in E2.. then here is a formula:

=MIN(VLOOKUP(1,A2:D4,3,),E2)*VLOOKUP(1,A2:D4,4,)+MIN(VLOOKUP(2,A2:D4,3,),MAX(0,E2-VLOOKUP(1,A2:D4,3,)))*VLOOKUP(2,A2:D4,4,)+MIN(VLOOKUP(3,A2:D4,3,),MAX(0,E2-VLOOKUP(1,A2:D4,3,)-VLOOKUP(2,A2:D4,3,)))*VLOOKUP(3,A2:D4,4,)

 

[Marcelo Branco]

Maybe...

	A​	B​	C​	D​	E​	F​	G​	H​
1​	rank​	value​	units sold​	unit value​		units replaced​		Qualified​
2​	3​	400​	2​	200​		4​		0​
3​	2​	1750​	5​	350​				3​
4​	1​	1200​	1​	1200​				1​
5​	Total​	3350​		1750​

Formula in H2 copied down

=MIN(C2,MAX(0,$F$2-SUMIF($A$2:$A$4,"<"&A2,$C$2:$C$4)))

Hope this helps

M.

 

[Slimm]

Thank you that works perfect!