Forum Discussion
Solved
Unable to get COUNTIFS to calculate properly if no data is present in criteria
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 calculation works:
=(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:
=(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")))
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.
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.
- This solution worked perfectly, and you were 100% correct about the nesting issue. Outstanding second set of eyes!!!
