Formula to sort through a range

[snoozing]

Not sure how to describe it but I'm trying to take predetermined pounds & multiply them against a rate. That rate is based on the state & range of the pound itself.

For example in the pictures, the green highlighted area is where I want the results to be. The orange highlights are the pounds. The blue higlights are the table I want the pounds to be referenced against. So for the 1/10/11 date in Atlanta, GA, the result should be (956 X $14.88)= $14,225.28

I know the sumif doesnt work with tables across multiple columns. Do I need to change my table? Is there a formula that will work here?

 

[iliace]

By what logic is the 956 multiplied by 14.88? Shouldn't it be 27.19 or something?

 

[snoozing]

No what I'm trying to accomplish is to have the formula look up the pounds in the orange highlights & determine the rate for it from the blue table & then multiply it against that rate to get the cost. So for the 1/10/11 date in Atlanta, GA, 956lbs puts the rate at $14.88. The product of 956 & 14.88 would go in the appropriate columns in the green area.

 

[iliace]

Ah, gotcha. So then, in your example you have Cincinnati where all the shipments are less than 100 lbs, is that what the minimum rate is for? In that case, the solution is fairly simple if you make that header say 0.

Excel Workbook
	A	B	C	D	E	F	G	H
1
2
3			Destination	1/10/2011	1/7/2011	1/24/2011	1/31/2011	2/7/2011
4			ANKORAGE,AK	$27,280.00	$31,856.00	$26,048.00	$36,080.00	$25,696.00
5			ATLANTA,GA	$14,225.28	$7,176.14	$4,810.91	$7,884.10	$8,704.80
6			CINCINNATI,OH	$636.30	$757.50	$606.00	$848.40	$606.00
7
8			ANKORAGE,AK	310	362	296	410	292
9			ATLANTA,GA	956	446	299	490	585
10			CINCINNATI,OH	21	25	20	28	20
11
12		Destination	0	100	250	500	1000	2000
13		ANKORAGE,AK	$95.00	$88.00	$88.00	$88.00	$88.00	$88.00
14		ATLANTA,GA	$31.54	$27.19	$16.09	$14.88	$14.33	$14.21
15		CINCINNATI,OH	$30.30	$26.66	$24.23	$21.85	$19.40	$18.21
Sheet