IF greater than, multiple by, and add range to next range multiplier.

[Velasdad]

We do some discounts for clients where we offer discounts on quantities. I have a column of figures and I can't for the life of me figure out how to add each range. I have the IF statement down to say, IF within this range THAN multiple by, but it doesn't add them together.

Example:
0-15 Items = Free
16-200 Items = $.75 each
201-500 Items = $.50 each
501+ = $.25 each

So I have =IF(A1<16, A1*0, IF(A1<201, A1*0.75, IF(A1<501, A1*0.5, A1*0.25))) Assuming "A"column are my amounts

Problem is that I want to add each range. So for 600 pieces, the first 15 are free, 16 thru 200 are .75 ea PLUS the $200 thru 500 are .50 PLUS the remaining 100 that are .25

How can I add these together?

[Ace_XL]

Use

=SUMPRODUCT(--(A1>{0;15;200;500}), (A1-{0;15;200;500}), {0;0.75;-0.25;-0.25})

Well explained here
http://www.mcgimpsey.com/excel/variablerate.html
http://www.excelforum.com/excel-form...lculation.html

[Velasdad]

Thanks Ace. You're my hero