COUNTIFS matching both of two criteria not either of two

caj1980

Board Regular
Joined
Oct 23, 2013
Messages
108
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]
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Your countifs give me 1 for the data you've shown as does this
=SUMPRODUCT((Table1[SOW REFERENCE]=T1)*(Table1[DATE CORRECTED]<>""))
 
Upvote 0
The SUMPRODUCT should be remediated as Fluff pointed out. The COUNTIFS should be:

=COUNTIFS(Table1[DATE CORRECTED],"<>",Table1[SOW REFERENCE],H1)

If you leave in the " " part, it will erroneously count 1 for the Safety row.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,224,820
Messages
6,181,155
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top