Replace value by cell number in sumproduct function

**DFHK** · 09-19-2019, 10:52 PM

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!

**alansidman** · 09-19-2019, 11:13 PM

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about.

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.)

**DFHK** · 09-19-2019, 11:24 PM

Hi Alan,

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

**Gregb11** · 09-20-2019, 12:14 AM

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

**DFHK** · 09-20-2019, 12:33 AM

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

**AliGW** · 09-20-2019, 12:41 AM

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

**DFHK** · 09-20-2019, 12:50 AM

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

**DFHK** · 09-20-2019, 12:58 AM

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

**AliGW** · 09-20-2019, 01:33 AM

So tell us what the output should be, please.

**AliGW** · 09-20-2019, 01:35 AM

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))

**DFHK** · 09-20-2019, 01:50 AM

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

**Root_** · 09-20-2019, 02:09 AM

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.

**DFHK** · 09-20-2019, 09:24 PM

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?

Replace value by cell number in sumproduct function

LinkBack

Thread Tools

Rate This Thread

Display

Replace value by cell number in sumproduct function

Re: Sumproduct

Re: Replace value by cell number in sumproduct function

Re: Replace value by cell number in sumproduct function

Re: Replace value by cell number in sumproduct function

Re: Replace value by cell number in sumproduct function

Re: Replace value by cell number in sumproduct function

Re: Replace value by cell number in sumproduct function

Re: Replace value by cell number in sumproduct function

Re: Replace value by cell number in sumproduct function

Re: Replace value by cell number in sumproduct function

Re: Replace value by cell number in sumproduct function

Re: Replace value by cell number in sumproduct function

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Sumproduct with multiple variants - Help! I'm a newbie to sumproduct

Combine sumif/sumproduct or sumproduct with multiple criteria

Count SUMPRODUCT Members / Average of SUMPRODUCT

Lookup or Sumproduct or if condition with sumproduct and vlookup

[SOLVED] Sumproduct with Multiple (Header) Criteria - Sumproduct, Index, Match

SUMPRODUCT: Help with text #VALUE, and INDIRECT(SUMPRODUCT)

Using a SumProduct Count to find a SumProduct Total?

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions