SUMPRODUCT for tiered pricing structure

I am having trouble returning the correct output for tiered pricing structure which is as follows;

$0-$300,000 0.30%
$300,001-$500,000 0.20%
$500,001-$1,000,000 0.10%
$1,000,001-$1,250,000 0.05%
$1,250,001+ 0.00%

I have used the following forumla;

=SUMPRODUCT(--(A9>{0,300000,500000,1000000,1250000,1250001}),--(A9-{0,300000,500000,1000000,1250000,1250001}),{0,0.003,-0.001,-0.001,-0.0005,-0.0005})

Where A9= $500,000.

The issue I am having is that the formula returns a value of $600 when it should returning $1,300 ($300,000*0.3%+$200,000+0.2%).

Any help to point out the floor in my formula would be much appreciated.

Thanks for the suggestion, comes back with $1,250, so still not quite the right figure.

Attachment is invalid, it won't open.

Originally Posted by BrianSheen

Attachment is invalid, it won't open.

Ignore this.

Originally Posted by harrisonland

Can it be done more elegantly than this: split this into two calculations: first calculate sums which apply when A9< upper limit of price band, then add an additional amount for the price band A9 falls into.

Formula:

Please Login or Register  to view this content.

I'm open to better suggestions!!!
Tim

This works perfectly, thanks for the help!