I've got an issue in Excel that I can't seem to work out:

I'm using SUMPRODUCT() function to calculate a sliding-scale commission. I've got the basics of it worked out by reading this page: <http://www.mcgimpsey.com/excel/variablerate.html>
which states that I can work out commission as so:

```
=SUMPRODUCT(--(A1>$J$2:$J$5), (A1-$J$2:$J$5), $L$2:$L$5)

```

Where A1 would be the gross amount, J2:J5 would be a range of thresholds and L2:L5 the different rates of commission on those thresholds.

I've got the formula working on a single test case, but when I try and factor out the variables into cell references and ranges it fails and returns #VALUE!

e.g. It fails when I use the following formula:

```
=SUMPRODUCT(--(C17>$C7:$E7),(C17-$C7:$E7),commissionPercentages)

```

or

```
=SUMPRODUCT(--(D17>$C7:$E7),(D17-$C7:$E7), R11:R13 )

```

both of which refer to three adjacent cell containing percentages.

However, if I hard-code the percentages as an array constant:

```
=SUMPRODUCT(--(C17>$C7:$E7),(C17-$C7:$E7),{0.05,0.05,0.1})

```

Then it works...

It also works in my test case where all the arguments are named references:

```
=SUMPRODUCT(--(testRevenueAmount>thresholds),(testRevenueAmount-thresholds),commissionPercentages)

```

I don't understand why this is happening, and what I've done wrong.

Could anyone enlighten me?

There is a simplified version of my spreadsheet here if you would like to see what I am attempting.

<http://diggory.net/Grazing/CommissionSample.xlsx>

Thanks.