Sum values, but not exceeding unit count

Slimm

New Member
Joined
Jun 26, 2014
Messages
10
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.

[TABLE="width: 415"]
<colgroup><col span="3"><col><col><col></colgroup><tbody>[TR]
[TD]rank[/TD]
[TD]value[/TD]
[TD]units sold[/TD]
[TD]unit value[/TD]
[TD] [/TD]
[TD]units replaced[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]400[/TD]
[TD]2[/TD]
[TD]200[/TD]
[TD] [/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1750[/TD]
[TD]5[/TD]
[TD]350[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1200[/TD]
[TD]1[/TD]
[TD]1200[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD]3350[/TD]
[TD] [/TD]
[TD]1750[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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")
 
Upvote 0
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
 
Last edited:
Upvote 0
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,)
 
Upvote 0
Maybe...


[Table="class: grid"][tr][td] [/td][td]
A
[/td][td]
B
[/td][td]
C
[/td][td]
D
[/td][td]
E
[/td][td]
F
[/td][td]
G
[/td][td]
H
[/td][/tr]
[tr][td]
1
[/td][td]
rank​
[/td][td]
value​
[/td][td]
units sold​
[/td][td]
unit value​
[/td][td] [/td][td]
units replaced​
[/td][td] [/td][td]
Qualified​
[/td][/tr]


[tr][td]
2
[/td][td]
3​
[/td][td]
400​
[/td][td]
2​
[/td][td]
200​
[/td][td] [/td][td]
4​
[/td][td] [/td][td]
0​
[/td][/tr]


[tr][td]
3
[/td][td]
2​
[/td][td]
1750​
[/td][td]
5​
[/td][td]
350​
[/td][td] [/td][td] [/td][td] [/td][td]
3​
[/td][/tr]


[tr][td]
4
[/td][td]
1​
[/td][td]
1200​
[/td][td]
1​
[/td][td]
1200​
[/td][td] [/td][td] [/td][td] [/td][td]
1​
[/td][/tr]


[tr][td]
5
[/td][td]
Total​
[/td][td]
3350​
[/td][td] [/td][td]
1750​
[/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]
[/table]


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.
 
Upvote 0

Forum statistics

Threads
1,223,391
Messages
6,171,822
Members
452,426
Latest member
cmachael

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top