# Might be as easy as I think...

##### New Member
So, there is an Excel form used around the office to track orders that we send out to different places, witht he total at the end. No problem so far, I know. The only issue, in our total we want our shipping costs included (We have 3 levels - Free, 10, and 15) and will remain constant for set clients. For example, We have 3 clients we offer free shipping to, another few that get the discounted rate of 10, and everyone else at 15. When I type in the column where the order was shipped to, is there a way of automatically having it identify a property with their shipping expense? Currently in the Total Box it looks something like this:

=SUM(F1:F95)+((20*0)+(25*10)+(50*15))

Each time a new entry is added, the person adding in the line scrolls to the bottom, and manually ups one of the numbers.

I'm thinking it could be as easy and nesting IF Statements, but if anyone has suggestions, I'd love to hear them.

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
One possibility:

Have another sheet with a named range "ShipRate" that refers to A:B

list the names of your clients in column A, ship rate in column B (0, 10, or 15).

Then use a vlookup to return the ship rate.

=VLOOKUP(Cell with customers name,ShipRate,2,0)
=VLOOKUP(A1,ShipRate,2,0) (if the cell with the customer's name is A1)

Hi.

You could have a list with the discounted customers and those with free shipments.

Formula in F12 is:
=SUMPRODUCT(F2:F10+(E2:E10<>"")*15-ISNUMBER(MATCH(E2:E10,I2:I7,0))*15-ISNUMBER(MATCH(E2:E10,J2:J7,0))*5)
Book1
EFGHIJK
2Cust1100Cust1Cust2
3Cust2101Cust4Cust12
4Cust3102Cust11Cust15
5Cust4103Cust18
6Cust1104Cust19
7Cust2105
8Cust3106
9Cust4107
10Cust9108
11
121001
13
Sheet1

Here's another way...

Assumptions:

Code:
``````E2:E95 contains the 'Customer Name'

F2:F95 contains the 'Amount'``````

B2:C10 contains a list of customers, along with corresponding fees...

Code:
``````B2:B10 contains the 'Customer Name'

C2:C10 contains the 'Shipping Fee'``````

Formula:

Code:
``=SUMPRODUCT(F2:F95+SUMIF(B2:B10,E2:E95,C2:C10))``

Hope this helps!

Replies
0
Views
160
Replies
4
Views
336
Replies
6
Views
934
Replies
0
Views
596
Replies
0
Views
152

1,196,487
Messages
6,015,482
Members
441,898
Latest member
kofafa

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