I am looking for advice on using Sumproduct and referencing cells

[BryanOpie]

I have managed to construct a formula that calculates a tiered discount based on the input of two cells (D6 & D7):

=(SUMPRODUCT(--(((D6+D7)/12)>{0,50000,150000,300000,500000,1000000}),--(((D6+D7)/12)-{0,50000,150000,300000,500000,1000000}),{0.55,-0.1,-0.1,-0.1,-0.1,-0.05}))*12

I am now looking to make the formula variable, i.e. rather than using values for bands and the pricing, I would like to reference cells for those values. When I try to replace a value with a cell reference, I get the following error:

There's a problem with this formula

Not trying to type a formula?
When the first character is an equal (=) or minus (-) sign, Excel thinks it's a formula:

• you type: =1+1, cell shows: 2

To get around this, type an apostrophe ( ' ) first:

• you type: '=1+1, cell shows: =1+1

Any suggestions?

[AliGW]

Welcome to the forum.

What does it look like with raw values? Paste the formula here so we can compare and troubleshoot.

[Glenn Kennedy]

Try this:

=SUMPRODUCT(--((($D$6+$D$7)/12)>$H$3:$H$8),--((($D$6+$D$7)/12)-$H$3:$H$8),$I$3:$I$8)*12

See sheet for context.

[BryanOpie]

Thank you so much Glenn!

That did the trick. Much obliged.

[Glenn Kennedy]

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.