# Replace value by cell number in sumproduct function

1. ## Replace value by cell number in sumproduct function

Hi There,

I am building a tiers pricing model with a rate that decrease depending on the number of hours commited. I have this formula using SUMPRODUCT that is working fine. However I would like to replace the numbers in the formula with cell so that when the cell number change I don't have to manually change the SUMPRODUCT formula. But when I do this I have error message poping up and don't seems to be able to link the cell to this formula

=SUMPRODUCT(--(C16>{0,10,15,20,30,50,200,500,1000,2000}),--(C16-{0,10,15,20,30,50,200,500,1000,2000}),{800,-64,-54,-46,-40,-34,-30,-26,-23,-20})

The first part of the formula is the range of hours
(--(C16>{0,10,15,20,30,50,200,500,1000,2000}),--(C16-{0,10,15,20,30,50,200,500,1000,2000})

I would like to simply replace each number with the cell where the number is

The second part of the formula is the rate
{800,-64,-54,-46,-40,-34,-30,-26,-23,-20})

I would like to replace the number with the cell and the formula (ie : H4-H3)

Thought appreciated

Thanks!

2. ## Re: Sumproduct

Welcome to the forum.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

3. ## Re: Replace value by cell number in sumproduct function

Hi Alan,

Thanks for the note. I have updated the title, please review and let me know if this is ok ... Thanks!

4. ## Re: Replace value by cell number in sumproduct function

I think this should work.
=SUMPRODUCT(--(C16>C15:L15),--(C16-C15:L15),C14:L14)

5. ## Re: Replace value by cell number in sumproduct function

Not quite .... it does not output the right result ...

6. ## Re: Replace value by cell number in sumproduct function

Did you enter it using CTRL+SHIFT+ENTER (not just ENTER)?

7. ## Re: Replace value by cell number in sumproduct function

Originally Posted by AliGW
Did you enter it using CTRL+SHIFT+ENTER (not just ENTER)?
yes I have done that but the output is not right .... I am trying to upload the file

8. ## Re: Replace value by cell number in sumproduct function

I have attached the file ... please take a look

9. ## Re: Replace value by cell number in sumproduct function

So tell us what the output should be, please.

10. ## Re: Replace value by cell number in sumproduct function

Maybe you need this:

=INDEX(\$C\$4:\$C\$13,MATCH(G2,\$A\$4:\$A\$13,1))

and this:

=INDEX(\$D\$4:\$D\$13,MATCH(G2,\$A\$4:\$A\$13,1))

11. ## Re: Replace value by cell number in sumproduct function

Originally Posted by Gregb11
I think this should work.
=SUMPRODUCT(--(C16>C15:L15),--(C16-C15:L15),C14:L14)
Originally Posted by AliGW
So tell us what the output should be, please.
In this case, there is a range of hours with a rate attached to each range. The hours' range and rate are nonlinear. For example

5 -10 hours ----- rate 1000
11 - 15 hours --- rate 950
16 - 20 hours --- rate 900
21 -30 hours --- rate 800

So if the order is 25 hours the rate will be calculated as follow

10 hours X \$1,000
5 hours X \$950
5 hours X \$900
5 hours X \$800

Total: 23,250. That will be the correct output which my formula return. When using the proposed formula then the result is 29,000

12. ## Re: Replace value by cell number in sumproduct function

In your file, replace the "High", "Min Rate", and "Max Rate" headers with zeros, and try the following formulas:

for Max Rate =SUMPRODUCT(--(G2>B3:B12),--(G2-B3:B12),ROUND(D4:D13-D3:D12,0))
for Min Rate =SUMPRODUCT(--(G2>B3:B12),--(G2-B3:B12),ROUND(C4:C13-C3:C12,0))

Those are normally-entered formulas -- no need to use Ctrl+Shift+Enter.

You may need to play with rounding.

13. ## Re: Replace value by cell number in sumproduct function

Originally Posted by Gregb11
I think this should work.
=SUMPRODUCT(--(C16>C15:L15),--(C16-C15:L15),C14:L14)
Originally Posted by Root_
In your file, replace the "High", "Min Rate", and "Max Rate" headers with zeros, and try the following formulas:

for Max Rate =SUMPRODUCT(--(G2>B3:B12),--(G2-B3:B12),ROUND(D4:D13-D3:D12,0))
for Min Rate =SUMPRODUCT(--(G2>B3:B12),--(G2-B3:B12),ROUND(C4:C13-C3:C12,0))

Those are normally-entered formulas -- no need to use Ctrl+Shift+Enter.

You may need to play with rounding.
Thanks root_ This works like a charm. However the rounding is creating some inaccurracy. How is the formula would like without the round dunction?

There are currently 1 users browsing this thread. (0 members and 1 guests)