=SumProducts() help please

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

??? Shouldn't the formula be

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

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

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:

Please Login or Register  to view this content.

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.

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.