Excel - Why is the SUMPRODUCT() function returning #VALUE! when I use a range or named range rather than an array constant?

0
votes

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.

2
excelexcel-formula
I've worked out what the issue was - most of the data was in horizontal arrays, but the commission percentages were in a vertical array - I didn't realise that arrays of different orientation could not be mixed in the SUMPRODUCT() function... - Diggory

2 Answers

3
votes

You can also use MMULT function to avoid "array entry", i.e.

=MMULT((C17>$C7:$E7)*(C17-$C7:$E7),commissionPercentages)

2
votes

As you commented, the different orientations is the issue.

You can work around this problem by adding a Transpose to your formula

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