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]="<>"&" "))
<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>
</tbody>
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]="<>"&" "))
SUMMARY OF ERRORS | Position | 1 | CORRECTED ERRORS | Position | 1 | ||||
Rack Feet | 1 | Rack Feet | 1 | ||||||
RPDU A/B Source | 1 | RPDU A/B Source | 1 | ||||||
RPDU Power | 1 | RPDU Power | 1 | ||||||
Device Power | 1 | Device Power | 1 | ||||||
Hot-Aisle | 1 | Hot-Aisle | 1 | ||||||
Safety | 1 | Safety | 0 | ||||||
Cusheets | 1 | Cusheets | 0 | OPEN ERRORS | |||||
Cable Routing | 1 | Cable Routing | 0 | 6 Open Errors (50%) | |||||
Device Groups | 1 | Device Groups | 0 | ||||||
Labels | 1 | Labels | 0 | ||||||
Test Results | 1 | Test Results | 0 | ||||||
TOTAL | 12 | TOTAL | 6 | ||||||
# | AZ | SITE | RACK | SOW REFERENCE | ISSUE | DATE REPORTED | DATE TASKED | DATE CORRECTED | CORRECTIVE ACTION |
1 | Position | 1-Dec-18 | |||||||
2 | Rack Feet | 2-Dec-18 | |||||||
3 | RPDU A/B Source | 3-Dec-18 | |||||||
4 | RPDU Power | 4-Dec-18 | |||||||
5 | Device Power | 5-Dec-18 | |||||||
6 | Hot-Aisle | 6-Dec-18 | |||||||
7 | Safety | ||||||||
8 | Cusheets | ||||||||
9 | Cable Routing | ||||||||
10 | Device Groups | ||||||||
11 | Labels | ||||||||
12 | Test Results |
<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>
</tbody>