Might be as easy as I think...

[shadwbaby]

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.

 

[J88L]

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)

 

[fairwinds]

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
	E	F	G	H	I	J	K
1	Customer	Amount			Customers that ship for free	Customers that ship for $10
2	Cust1	100			Cust1	Cust2
3	Cust2	101			Cust4	Cust12
4	Cust3	102			Cust11	Cust15
5	Cust4	103				Cust18
6	Cust1	104				Cust19
7	Cust2	105
8	Cust3	106
9	Cust4	107
10	Cust9	108
11
12		1001
13
Sheet1

 

[Domenic]

Here's another way...

Assumptions:

E2:F95 contains your data...

E2:E95 contains the 'Customer Name'

F2:F95 contains the 'Amount'

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

B2:B10 contains the 'Customer Name'

C2:C10 contains the 'Shipping Fee'

Formula:

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

Hope this helps!