Single SUMIFS and COUNTIFS work, but two SUMIFS and two COUNTIFS do not work

Question

I'm exporting data for a manufacturing environment and importing the CSV into Excel. This data refreshes every 5 minutes.

On the first worksheet, I'm able to calculate efficiency % properly (N:N) with a single SUMIFS and a single COUNTIFS. However, when I try to add two SUMIFS and divide by two added COUNTIFS the calculation reports an incorrect result. I should add that there are times when either "HMP20 1" or "GHMP20 1" are not present.

This formula returns the expected result 100.83:

=(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, "GHMP20 1")))

This calculation returns an incorrect result: 388.61:

=(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")))

Sample Data

Worksheet: "EP2P Mold Summary All Shifts"

	B	C	D - M	N
1	Shift	Machine		EFF %
2	1	GHMP20 1		101
3	1	GHMP20 1		98
4	1	HMP20 1		112
5	3	HMP20 1		87
6	3	SINTO 7		92
7	3	GSINTO 7

_{Data worksheet: "EP2P Mold Summary All Shifts"}

_{"Results Worksheet: "Mold Summary All Shifts"}

Answer 1

The mean can be calculated using the AVERAGE function

The FILTER function will return a subset of your data based on conditions.

Making these two changes should make it much simpler for you to review and extend your formula.

1.  "GHMP20 1" Only

^{! -> Asterisk * is equivalent to AND}

      1a.   IFERROR : "" If No Match

=IFERROR(
   AVERAGE(FILTER(N:N,(B:B=1)*
     (C:C="GHMP20 1"))),"")

      1b.   FILTER : 0 If No Match

=AVERAGE(FILTER(N:N,(B:B=1)*
   (C:C="GHMP20 1"),0))

2.  "GHMP20 1" & "HMP20 1"

^{! -> Plus symbol + is equivalent to OR}

      2a.   IFERROR : "" If No Match

=IFERROR(
   AVERAGE(FILTER(N:N,(B:B=1)*
     ((C:C="GHMP20 1")+(C:C="HMP20 1"))),"")

      2b.   FILTER : 0 If No Match

=AVERAGE(FILTER(N:N,(B:B=1)*
   ((C:C="GHMP20 1")+(C:C="HMP20 1")),0))