Good Morning Everyone,
I am having some difficulty with the SumProduct formula. I have attached a very basic dumb down version of my workbook so I do not confuse anyone trying to help or learn.

The sample i have provide has two worksheets. "master" and "forecast". My goal here is to sum all the values on "forecast" in rows 4-8. Only if two dates for each column fall within the date range I entered on the sheet title Master. I have got this to work by using the following formula:

=SUMPRODUCT((Forecast!\$C\$2:\$S\$2>=E\$3)*(Forecast!\$C\$3:\$S\$3<=E\$4),Forecast!\$C4:\$S4)

My problem is that I cannot get formula to calculate properly between the date ranges if the date range I enter falls inside of the dates of each Job.

To help explain further what I am trying to do. If you looks at "forecast" each job has a delivery date and a return date. On the sheet "Master" If I enter in Row 3 and row 4 of Column D through J dates I want to see what is still out on a job. If you see in Column G on the "Master" sheet my sums are not adding correctly. I have entered 1/1/15 -2/3/2015. The formula i am using is simply calculating everything that is out except the first job with returns on 2/5/2015. I need the formula to recognize that Job 1 is still out and need to be added to the rest of the counts even though I have asked to calculate from 1/1/2015 -2/3/2015. I apologize if I just confused everyone. And I greatly appreciate any help.

Thank you,
JBTest Page 3.xlsx

3. ## Re: =SumProducts() help please

Sorry John. I'm slightly challenged. This should work.

Test Page 3.xlsx

4. ## Re: =SumProducts() help please

I'm challenged too! I don't understand why Job 1 has to be included even though it isn't in your date range.Jobs 16 & 17 are also out of range but not to be included.

5. ## Re: =SumProducts() help please

??? Shouldn't the formula be

=SUMPRODUCT((Forecast!\$C\$2:\$S\$2>=D\$3)*(Forecast!\$C\$3:\$S\$3<=\$D\$4),Forecast!\$C4:\$S4)

6. ## Re: =SumProducts() help please

Ignore 16 and 17 for now. Job 1 oes fall with in the criteria that i am trying to sum. the sum i need is a reflection of what is currently out on a job and not in the inventory. the products will continue to be counted as out until they return on the date entered on the forecast sheet. If a Job leaves on 1/1/2015 and is scheduled to return on 2/5/2015, then if I ask the cell to sum all jobs between 1/1/2015 - 2/3/2015. Any Job that is out between those dates need to counted for. In other words If a job delivers the 12/13/2014 but returns on 2/5/2015. That job should still be sum'd if i enter 1/1/2015 - 2/3/2015 given that this product is still out on a job. I hope this clears up my goal and didn't confuse everyone more.

7. ## Re: =SumProducts() help please

Originally Posted by Glenn Kennedy
??? Shouldn't the formula be

=SUMPRODUCT((Forecast!\$C\$2:\$S\$2>=D\$3)*(Forecast!\$C\$3:\$S\$3<=\$D\$4),Forecast!\$C4:\$S4)
Hi Glen,
Column D on "master" is simply showing me what is out from today until the end of the year. My concern is with Column G where I should be getting on master! D5 = 595 not 535. currently it is not calculating the "60" that is on Forecast! C4. which needs to be, because I want to calculate what is still out between the dates

8. ## Re: =SumProducts() help please

Could you input manually your desired values in sheet Master from cell D5 to J5?

9. ## Re: =SumProducts() help please

Not certain I understand the concept but this adds the 60 to column G. It also adds 60 to columns F and H. It also adds 35 to column I (15 and 20). This is an array-entered into D5 filled down and across.
Formula:
If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

10. ## Re: =SumProducts() help please

Originally Posted by bebo021999
Could you input manually your desired values in sheet Master from cell D5 to J5?
I am trying to eliminate the manual entry. The sheet i uploaded is just a small template of a workbook with 18 worksheets that all ultimately connect to the master sheet. also the list of jobs I have on the template is about an 1/8th of what is actually entered each month and all the dates vary. So you see I am trying to figure out a way to get excel to calculate this for me instead of spending hours upon hours each day manually doing it.

11. ## Re: =SumProducts() help please

Originally Posted by JB20
I am trying to eliminate the manual entry.
Ha ha
Just show your desired results, helpers will show you how to get them.

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