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
Bookmarks