SUMIFS range criteria filters

[mannyghee]

I have an Excel database query where I extract production runs (date run ended, volumes, product line, etc., which are then recorded in a worksheet called "EORData". In another worksheet, I have a week table where I summarize the data statistics:Table Column A: Production Date (formatted as mm/dd/yyyy)Table Column B through E: different product linesUnder each product lines are the volumes totaled for the production date for the whole week. The file is ran every day to show what were processed the day before.The following formula is what is in each cell under each product line for each day of the week:=(SUMIFS(EORData!$J:$J,EORData!$D:$D,C$4,EORData!$H:$H,">01/01/2016 08:00:00 PM",EORData!$H:$H,"<01/02/2016 12:00:00 PM"))where:EORData!$J:$J is the column that has the total volumes processedC$4 is the column header that has the name of the product line in the report summary sheetEORData!$D:$D is the column that has the product line nameEORData!$H:$H is the column that has the production run end date and timeAs written in the example code above, the row was for 01/02/2016 and actually sums the production volume processed between 01/01/16 08:00 pm and 01/02/16 12:00 pm.My situation is this: I have to manually change the dates in the formulas (using Find/Replace) for each day of the week for the weekly file. I am looking for a value substitution where I will insert in the formula the date found in Column A and it will automatically replace the range criteria filter values in the formula. I tried every possibility and hit a roadblock. I used the "" and () combinations with no success. Once I figured this out, all I have to do is just change the dates on Column A and the cell formulas will be automatically reconfigured.Thanks for any help.

 

[jorismoerings]

Hi,

try this:
=SUMIFS(eordata!$J:$J,eordata!$D:$D,C$4;eordata!$H:$H,">"&A1+"08:00:00 am",eordata!$H:$H,"<"&A2+"12:00:00 pm")