I am trying to calculate the number of remaining errors in a list of total errors, sorted by type of error, as follows. I have tried both COUNTIFS and SUMPRODUCT with no success. Help Please!!
Target Cell for the formula is I1. I need it to calculate the number of cells in column I table (Date Corrected) below that match H1 and are not blank.
I have tried the following with no success:
=COUNTIFS(Table1[DATE CORRECTED],"<>"&" ",Table1[SOW REFERENCE],H1)
=SUMPRODUCT((Table1[SOW REFERENCE]=H1)*(Table1[DATE CORRECTED]="<>"&" "))
[TABLE="class: grid, width: 500"]
<colgroup><col style="mso-width-source:userset;mso-width-alt:917;width:22pt" width="29"> <col style="mso-width-source:userset;mso-width-alt:1301;width:31pt" width="41"> <col style="mso-width-source:userset;mso-width-alt:1770;width:42pt" width="55"> <col style="mso-width-source:userset;mso-width-alt:5269;width:124pt" width="165"> <col style="mso-width-source:userset;mso-width-alt:5269;width:124pt" width="165"> <col style="mso-width-source:userset;mso-width-alt:12330;width:289pt" width="385"> <col style="mso-width-source:userset;mso-width-alt:4224;width:99pt" width="132"> <col style="mso-width-source:userset;mso-width-alt:4053;width:95pt" width="127"> <col style="mso-width-source:userset;mso-width-alt:4501;width:106pt" width="141"> <col style="mso-width-source:userset;mso-width-alt:11840;width:278pt" width="370"> </colgroup><tbody>[TR]
[TD="width: 29"][/TD]
[TD="width: 41"][/TD]
[TD="width: 55"]SUMMARY OF ERRORS[/TD]
[TD="width: 165"]Position[/TD]
[TD="width: 165"]1[/TD]
[TD="width: 385"][/TD]
[TD="width: 132"]CORRECTED ERRORS[/TD]
[TD="width: 127"]Position[/TD]
[TD="width: 141"]1
[/TD]
[TD="width: 370"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Rack Feet[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]Rack Feet[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]RPDU A/B Source[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]RPDU A/B Source[/TD]
[TD]1
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]RPDU Power[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]RPDU Power[/TD]
[TD]1
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Device Power[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]Device Power[/TD]
[TD]1
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Hot-Aisle[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]Hot-Aisle[/TD]
[TD]1
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Safety[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]Safety[/TD]
[TD]0
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Cusheets[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]Cusheets[/TD]
[TD]0
[/TD]
[TD]OPEN ERRORS[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Cable Routing[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]Cable Routing[/TD]
[TD]0
[/TD]
[TD]6 Open Errors (50%)
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Device Groups[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]Device Groups[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Labels[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]Labels[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Test Results[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]Test Results[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]TOTAL[/TD]
[TD]12[/TD]
[TD][/TD]
[TD]TOTAL[/TD]
[TD]6
[/TD]
[/TR]
[TR]
[TD="colspan: 10"] [/TD]
[/TR]
[TR]
[TD]#[/TD]
[TD]AZ[/TD]
[TD]SITE[/TD]
[TD]RACK[/TD]
[TD]SOW REFERENCE[/TD]
[TD]ISSUE[/TD]
[TD]DATE REPORTED[/TD]
[TD]DATE TASKED[/TD]
[TD]DATE CORRECTED[/TD]
[TD]CORRECTIVE ACTION[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Position[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1-Dec-18[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Rack Feet[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]2-Dec-18[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]RPDU A/B Source[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]3-Dec-18[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]RPDU Power[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]4-Dec-18[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Device Power[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]5-Dec-18[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Hot-Aisle[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]6-Dec-18[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Safety[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Cusheets[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Cable Routing[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Device Groups[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Labels[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Test Results[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
Target Cell for the formula is I1. I need it to calculate the number of cells in column I table (Date Corrected) below that match H1 and are not blank.
I have tried the following with no success:
=COUNTIFS(Table1[DATE CORRECTED],"<>"&" ",Table1[SOW REFERENCE],H1)
=SUMPRODUCT((Table1[SOW REFERENCE]=H1)*(Table1[DATE CORRECTED]="<>"&" "))
[TABLE="class: grid, width: 500"]
<colgroup><col style="mso-width-source:userset;mso-width-alt:917;width:22pt" width="29"> <col style="mso-width-source:userset;mso-width-alt:1301;width:31pt" width="41"> <col style="mso-width-source:userset;mso-width-alt:1770;width:42pt" width="55"> <col style="mso-width-source:userset;mso-width-alt:5269;width:124pt" width="165"> <col style="mso-width-source:userset;mso-width-alt:5269;width:124pt" width="165"> <col style="mso-width-source:userset;mso-width-alt:12330;width:289pt" width="385"> <col style="mso-width-source:userset;mso-width-alt:4224;width:99pt" width="132"> <col style="mso-width-source:userset;mso-width-alt:4053;width:95pt" width="127"> <col style="mso-width-source:userset;mso-width-alt:4501;width:106pt" width="141"> <col style="mso-width-source:userset;mso-width-alt:11840;width:278pt" width="370"> </colgroup><tbody>[TR]
[TD="width: 29"][/TD]
[TD="width: 41"][/TD]
[TD="width: 55"]SUMMARY OF ERRORS[/TD]
[TD="width: 165"]Position[/TD]
[TD="width: 165"]1[/TD]
[TD="width: 385"][/TD]
[TD="width: 132"]CORRECTED ERRORS[/TD]
[TD="width: 127"]Position[/TD]
[TD="width: 141"]1
[/TD]
[TD="width: 370"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Rack Feet[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]Rack Feet[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]RPDU A/B Source[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]RPDU A/B Source[/TD]
[TD]1
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]RPDU Power[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]RPDU Power[/TD]
[TD]1
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Device Power[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]Device Power[/TD]
[TD]1
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Hot-Aisle[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]Hot-Aisle[/TD]
[TD]1
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Safety[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]Safety[/TD]
[TD]0
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Cusheets[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]Cusheets[/TD]
[TD]0
[/TD]
[TD]OPEN ERRORS[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Cable Routing[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]Cable Routing[/TD]
[TD]0
[/TD]
[TD]6 Open Errors (50%)
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Device Groups[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]Device Groups[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Labels[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]Labels[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Test Results[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]Test Results[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]TOTAL[/TD]
[TD]12[/TD]
[TD][/TD]
[TD]TOTAL[/TD]
[TD]6
[/TD]
[/TR]
[TR]
[TD="colspan: 10"] [/TD]
[/TR]
[TR]
[TD]#[/TD]
[TD]AZ[/TD]
[TD]SITE[/TD]
[TD]RACK[/TD]
[TD]SOW REFERENCE[/TD]
[TD]ISSUE[/TD]
[TD]DATE REPORTED[/TD]
[TD]DATE TASKED[/TD]
[TD]DATE CORRECTED[/TD]
[TD]CORRECTIVE ACTION[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Position[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1-Dec-18[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Rack Feet[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]2-Dec-18[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]RPDU A/B Source[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]3-Dec-18[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]RPDU Power[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]4-Dec-18[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Device Power[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]5-Dec-18[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Hot-Aisle[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]6-Dec-18[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Safety[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Cusheets[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Cable Routing[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Device Groups[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Labels[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Test Results[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]