SUM of only Filtered columns/Row IF between specific dates range

[johnsonjohnson]

I have a shipment report that I'd like to calculate 2 different totals for and add the data at the top of the report.

I need help determining how to In Criteria Group B - Filtered Group section on the image only returning the values for the filtered Count of Shipments and Sum of Eaches for 3 different categories:

I don't know how to even approach these three formulas:

A. Sum Eaches is Sum of the total eaches in the shipments broken down into 3 categories:
1. For filtered cells only Sum total eaches if Days Early/Late is On Time (o+ days in column J).
2. For filtered cells only Sum total eaches if Days Early/Late is+7 Days Late (<-6 and >-14 days in column J)
3. For filtered cells only Sum total eaches if Days Early/Late is +14 Days Late (<-13 in Column J)


B. Count Shipments is Count of the total number of shipments broken down into 3 categories:
1. For filtered cells only Count # shipments if On Time (o+ days in column J). I tried Formula =SUMPRODUCT(SUBTOTAL(102,OFFSET($J$7:$J$2947,ROW($J$7:$J$2947)-MIN(ROW($J$7:$J$2947)),,1))*($J$7:$J$2947>-1))

2. For filtered cells only Count # shipments if +7 Days Late (<-6 and >-14 days in column J). I can't seem to get the formula to only return for values between days -6 and -14.

3. For filtered cells only Count # shipments if +14 Days Late (<-13 in Column J). I tried Formula =SUMPRODUCT(SUBTOTAL(102,OFFSET($J$7:$J$2947,ROW($J$7:$J$2947)-MIN(ROW($J$7:$J$2947)),,1))*($J$7:$J$2947<-13))

 

[arthurbr]

Perhaps the AGGREGATE or SUBTOTAL function ? ( depending on your version)

 

[Aladin Akyurek]

Try this between interpretation...

=SUMPRODUCT(SUBTOTAL(103,OFFSET($J$7,ROW($J$7:$J$2947)-ROW($J$7),0,1)),--($J$7:$J$2947>=-14),--($J$7:$J$2947<=-6))