tiered pricing calculator in excel

Here's one way to do this, using SUMPROUCT. the formula is

=B1*SUMPRODUCT(--(B2>D2:D5),--(B2-D2:D5),--(G2:G5))

the setting is shown in the attachment.

The rate table in the examples has an error in my opinion. If the value in A1 is equal the any of the values in column D then the result is 0 so in effect all values at the bottom of the ranges are being calculated at a vastly different rate than other values within the same range.

Using your example if A1=11 Amount is 1530 if A1 is 12 the amount is 170 etc

	B	C
9	11	1530
10	12	170
11	13	340
12	14	510
13	15	680
14	16	850

Perhaps like this?

Alf