# Total Formula

#### SwapShop

##### New Member
I'm currently trying to build a spreadsheet for my consignment vendors in order to keep track of their items in a database format. I am stumped at one particular formula for the total.

Many items have more than one quantity (same item category, description, and price) which I want in the same row (to avoid the same information having to be entered multiple times).

Some of these items may or may not be sold at a discounted price.

I want to take the price of the item and multiply it by the quantity sold and subtract the discount (percentage) for the quantity sold at the discount and not the items sold at full price.

for example: I have 5 books being sold for \$10 each (H3), 3 were total have been sold (I3), two of them (K3) were sold at 50% off (J3) and one was sold at full price (reverting back to I3 as only 2 were sold at a discounted price)...the total should be \$20.

If the qty of the discounted items changed to 3 the total should change to \$15.

I can't for the life of me figure out how the math should work in order to get the correct formula... any help would greatly be appreciated.

### Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

#### SwapShop

##### New Member
I came up with a formula that does work. I don't know if there's something that would simplify it or not.

=(I3-J3)*(K3)-(I3-J3)*(1-L3)*M3

#### SwapShop

##### New Member
I lied! It doesn't exactly work like it should. I was using 50% OFF as an example since that's currently a sale we're providing... at 50% off it does work...

This is the formula I have come up with
=(I3-J3)*(K3)-(I3-J3)*(L3)*M3

the other formula only told me how much I should subtract from the final total.

Replies
9
Views
2K
Replies
4
Views
416
Replies
1
Views
375
Replies
5
Views
203
Replies
2
Views
340

1,191,383
Messages
5,986,304
Members
440,017
Latest member
vasanrajeswaran

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