Sumproduct Function with greater or less than formula?

**excellenthelp** · 08-15-2013, 03:54 PM

Hello all,

I am having a difficult time writing the SUMPRODUCT formula in C29 on sheet "Template" where:

If the request is made more than 5 days (D3), then sumproduct will use the rate in sheet Sample!F3:H3 and insert the rate in B29,C29 and D29.

I know column D:D needs to be changed... but to what??

Same for if the request is made less than 5 days.

my written SUMPRODUCT formula:

Formula:

Please Login or Register  to view this content.

The issue is in "D3" in the formula above: ...(WhatIf!$C:$C="D3")...

But if I change D3 to "5>", it works. I do not want to do that manually... instead have the formula calculate if it is more or less than 5 days then do this or do that... blah blah.

Below is an attached Sample Workbook of my issue.

SampleHelp.xlsx

Much appreciation!!

**popipipo** · 08-15-2013, 04:13 PM

I should use whole numbers in 'WhatIf!$C:$C'

By the way i am missing what you want if 2 Days in Advance

**Ace_XL** · 08-15-2013, 04:29 PM

Try

=SUMPRODUCT(--(WhatIf!$A:$A=$D$13),--(WhatIf!$B:$B=$C$8),--(WhatIf!$C:$C=$D$3&">"),--(WhatIf!$D:$D=$B$6),WhatIf!E:E)

Change the highlighted signs as per requirement or perhaps even reference that to a separte cell

**excellenthelp** · 08-15-2013, 04:41 PM

Ace,

Thank you for your help. This is the closet solution and I am interested in digging more.

With the suggested formula, it will not be able to search in the table in sheet WhatIf because that reference does not exist... To make this clearer, I made some adjustments in the sheet.

Here are four scenarios I would like to run:

Scenario A:
If the request is made 6 days before the day of assignment for only one hour.
B3: 8/1/13
B4: 8/7/13

The results should show:
C24: BBB
C25: $171.00

Scenario B:
If the request is made 4 days before the day of assignment for only one hour.
B3: 8/1/13
B4: 8/5/13

The results should show:
C24: BBB
C25: $171.00

Scenario C:
If the request is made 2 days before the day of assignment for only one hour.
B3: 8/1/13
B4: 8/3/13

The results should show:
C24: BBB
C25: $214

Scenario D:
If the request is made 2 days before the day of assignment for only one hour.
B3: 8/1/13
B4: 8/1/13

The results should show:
C24: BBB
C25: $356

So in B29, C29 and D29, there is a formula that needs to be edited/improved.

Formula:

Please Login or Register  to view this content.

The issue lies in "5+ Days"
AND also in column C:C... how may I write this to better match D3 in sheet 'Template' so the calculation could be only numeric and dynamic?

Hope I am more clear this time.

Attached worksheet:
SampleHelpv1.xlsx

**newdoverman** · 08-15-2013, 07:53 PM

I have tried a few things with your worksheet. I found nothing referring to BBB in the workbook as you mention for B24.

The SUMPRODUCT formulae in row 29 refer to the wrong columns in the other worksheet (lookup values don't match the values in the referenced columns)

I eliminated the < and > signs from the column for days in column C of Whatif and used the <, > logic in the formulae addressing this column.

This might not solve your problems but it should give you an idea as to how to solve the problems.

**excellenthelp** · 08-16-2013, 08:38 AM

Newdoverman,

Thank you for addressing my issue. I uploaded an updated version of the workbook above your post (SampleHelpv1.xlsx). It has exactly what we are looking for... you downloaded the older version which was more confusing. =D

Let's give this another shot.

What I am looking for exactly is to change this part in the formula:

Formula:

Please Login or Register  to view this content.

I want to change the red highlighted part to refer to "D3" instead of "5+ Days". So when the user inserts the dates in B3 and B4, D3 will calculate the number of days in between those two dates and then B29,C29 and D29 will include D3 in its calculation instead of manually changing the < and > signs in the formula.

**excellenthelp** · 08-16-2013, 08:58 AM

maybe try something with IF statement:
IF >=5 THEN do sumproduct with "5<" instead of "5+ Days"
IF <=5 THEN do sumproduct with "5>" instead of "3 - 5 Days"
IF >=3 THEN do sumproduct with "3<" instead of "1 - 3 Days"

(and change D:D in sheet "WhatIf" to 5<. 5>, 3<, etc. so sheet Template can calculate the SUMPRODUCT correctly.

Or something...? I am not sure how to write this in the forumla of cells B29, C29 and D29.

**newdoverman** · 08-16-2013, 09:59 AM

I have figured a way around some of the problem. There is however a problem that you will have to figure out before it can be taken further. You have conditions of overlapping times. That produces a dilemma that can't be resolved. You have 4 time periods that apply throughout the WhatIf column C. They are:

1 Day or less
1 to 3 Days
3 to 5 Days
5+ Days

1 Day is in the first condition and is also in the second condition
3 days is in the second condition and is also in the third condition
5 days is in the third condition and is also in the fourth condition.

The scenarios that you give, have incorrect results if you follow the chart on the WhatIf worksheet.

Scenario A is OK. Scenario B should be 214 and not 171. Scenario C is correct. Scenario D shows 2 days when there is only 1 day and the value of 356 doesn't show in your chart.

I think that if you can resolve the times in the chart and make the changes in WhatIf column C to match then your formulae will work out.

**excellenthelp** · 08-16-2013, 11:15 AM

I updated the report after sitting for a long time trying to figure out how to write a better formula in E18:G18.

Updated formula:

Formula:

Please Login or Register  to view this content.

However, this formula still have an issue with scenarios C and D.

Here are four scenarios:

Scenario A:
If the request is made 6 days before the day of assignment for only one hour.
B3: 8/1/13
B4: 8/7/13

The results should show:
E17:G17: AAA BBB CCC
E18:G18: $243 $171 $304

Scenario B:
If the request is made 4 days before the day of assignment for only one hour.
B3: 8/1/13
B4: 8/5/13

The results should show:
E17:G17: AAA BBB CCC
E18:G18: $243 $214 $304

Scenario C:
If the request is made 2 days before the day of assignment for only one hour.
B3: 8/1/13
B4: 8/3/13

The results should show:
E17:G17: AAA BBB CCC
E18:G18: $315 $214 $345

Scenario D:
If the request is made 2 days before the day of assignment for only one hour.
B3: 8/1/13
B4: 8/1/13

The results should show:
E17:G17: AAA BBB CCC
E18:G18: $315 $310 $446

How may we improve the formula to calculate this better?

Attached updated workbook:
SampleHelpv2.xlsx

**popipipo** · 08-16-2013, 02:15 PM

Take a look at this.

**excellenthelp** · 08-16-2013, 03:17 PM

Thanks guys!!

Marking this as SOLVED.

Sumproduct Function with greater or less than formula?

LinkBack

Thread Tools

Rate This Thread

Display

Sumproduct Function with greater or less than formula?

Re: Sumproduct Function with greater or less than formula?

Re: Sumproduct Function with greater or less than formula?

Re: Sumproduct Function with greater or less than formula?

Re: Sumproduct Function with greater or less than formula?

Re: Sumproduct Function with greater or less than formula?

Re: Sumproduct Function with greater or less than formula?

Re: Sumproduct Function with greater or less than formula?

Re: Sumproduct Function with greater or less than formula?

Re: Sumproduct Function with greater or less than formula?

Re: Sumproduct Function with greater or less than formula?

Thread Information

Users Browsing this Thread

Similar Threads

when using IF function, what formula returns a value no greater than a set target?

[SOLVED] Sumproduct to count if greater than

Formula/function to input value when greater than more than 2

Sumproduct with Greater Than Less Than

GREATER OR EQUAL TO BUT LESS THAN Problem using Sumproduct

Bookmarks

Bookmarks

Posting Permissions