Unable to get COUNTIFS to calculate properly if no data is present in criteria

I believe your problem is the ( ) nesting. Try this:

=(SUMIFS('EP2P Mold Summary All Shifts'!N:N,'EP2P Mold Summary All Shifts'!B:B,"1",'EP2P Mold Summary All Shifts'!C:C,"HMP20 1")+
   SUMIFS('EP2P Mold Summary All Shifts'!N:N,'EP2P Mold Summary All Shifts'!B:B,"1",'EP2P Mold Summary All Shifts'!C:C,"GHMP20 1")) / 
  (COUNTIFS('EP2P Mold Summary All Shifts'!N:N,">0",'EP2P Mold Summary All Shifts'!B:B,"1",'EP2P Mold Summary All Shifts'!C:C,"HMP20 1")+
   COUNTIFS('EP2P Mold Summary All Shifts'!N:N,">0",'EP2P Mold Summary All Shifts'!B:B,"1",'EP2P Mold Summary All Shifts'!C:C,"GHMP20 1"))

of maybe upgrade to Excel 365 functions:

=LET(in, 'EP2P Mold Summary All Shifts'!B:N,
AVERAGE(FILTER(TAKE(in,,-1),
             (INDEX(in,,1)="1")* ((INDEX(in,,2)="HMP20 1")+(INDEX(in,,2)="GHMP20 1")) * (TAKE(in,,-1)>0),
              0)))

 I would also highly recommend formatting as a Table and then using table structured references so those references (a) use header names to make sense and (b) don't need to reference the entire column that has tons of blank cells.