# \$ Total a Quantity from Lowest to Highest Price

##### New Member
Hello everyone. I am really stumped on this problem and I am not even sure if a macro is needed to solve it. Sure looks like it though.

 A B C D E F G H I J K 1 Current Stock Sale 2 Item Type Qty Price Total Item Qty Total Calculation: 3 A Good 6 \$5.00 \$30.00 A 3 \$1.00 =2@\$0 + 1@\$1 4 A Good 2 \$4.00 \$8.00 A 2 \$2.00 =2@\$1 5 A Bad 2 \$0.00 \$0.00 A 9 \$27.00 =4@\$1 + 2@\$4 + 3@\$5 6 A Bad 7 \$1.00 \$7.00 B 7 \$40.00 =2@\$0 +5@\$8 7 B Good 8 \$8.00 \$64.00 8 B Bad 2 \$0.00 \$0.00

<tbody>
</tbody>

In this sheet there is a Current Stock table and a Sale table. The cells J3:J6 will initially be empty and this is what I would like to populate.

The Total in column J for each Item should be a dollar amount such that it equals the sum of [the lowest price of that item in the Current Stock table times the available quantity of that price]

Therefore, item A in cell H6 with a quantity of 3 will have a dollar total (2 x \$0 + 1 x \$1). Since there were only 2 units at \$0 each in the current stock table, there is no available stock at this price anymore, while there remains 6 left of A priced at \$1.

Thus, item A in cell H7 with a quantity of 2 will have a dollar total of (2 x \$ 1). Now there are only 4 left of item A priced at \$1 each in the Current Stock table.

As such, item A in cell H8 with a quantity of 9 will have a dollar total of (4 x \$1 + 2 x \$4 + 3 x \$5).

And so on and so forth for the rest of the items.

In another sheet below, the requirement is similar, but this time, there is a Type condition in the sale, which restricts the pricing of the item at the specified type.

 A B C D E F G H I J K 1 Current Stock Sale 2 Item Type Qty Price Total Item Type Qty Total Calculation: 3 A Good 6 \$5.00 \$30.00 A Bad 3 \$1.00 =2@\$0 + 1@\$1 4 A Good 2 \$4.00 \$8.00 A Good 2 \$8.00 =2@\$4 5 A Bad 2 \$0.00 \$0.00 B Bad 2 \$0.00 =2@\$0 6 A Bad 7 \$1.00 \$7.00 7 B Good 8 \$8.00 \$64.00 8 B Bad 2 \$0.00 \$0.00

<tbody>
</tbody>

I hope that made sense. I have am having a really hard time figuring out how to do this. Any help/guidance would be very much appreciated.

### Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Replies
4
Views
437
Replies
4
Views
265
Replies
2
Views
286
Replies
9
Views
2K
Replies
0
Views
504

1,195,582
Messages
6,010,581
Members
441,557
Latest member
Jbest23

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