# Sum values, but not exceeding unit count

#### Slimm

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

### Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

#### LxQ

##### Well-known Member
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

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

Last edited:

#### LxQ

##### Well-known Member
Got it... how many of these ranks can you have?

#### LxQ

##### Well-known Member
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

##### MrExcel MVP
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

##### New Member
Thank you that works perfect!

Replies
1
Views
678
Replies
3
Views
439
Replies
5
Views
996
Replies
0
Views
492
Replies
4
Views
514

1,195,681
Messages
6,011,129
Members
441,586
Latest member
rodsin76

### 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.

### Which adblocker are you using?

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

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