COUNTIFS function with ANDs and ORs

**Deutz** · 06-26-2014, 03:36 AM

Hi and thanks in advance.

I am trying to construct a COUNTIFS function that utilizes ANDs and ORs without much success.

I have a sheet that has a 1000 rows of data and I am trying to count the number of rows where the following is true ...

Column A = "WP"
AND
Column B <> "Target" AND Column B <> "Sorted"
AND
Column C = "Second"
AND
(Column D = "Fail" OR Column E = "Fail" OR Column F = "Fail" OR Column G = "Fail" OR Column H = "Fail" OR Column I = "Fail")

Started mucking about with COUNTIFS and curly brackets and SUM function but not really sure how to do this.

Thanks

**benishiryo** · 06-26-2014, 03:57 AM

hi Deutz. you can use this if "fail" only appears once:
=SUMPRODUCT((A2:A20="WP")*(B2:B20<>"Target")*(B2:B20<>"Sorted")*((D2:I20="Fail")))

or this if it can appear more than once in any columns:

Formula:

Please Login or Register  to view this content.

**samba_ravi** · 06-26-2014, 03:57 AM

=COUNTIFS(A1:A1000,"WP",B1:B1000,"<>"&"Target",B1:B1000,"<>"&"Sorted",C1:C1000,"Second")-COUNTIFS(A1:A1000,"WP",B1:B1000,"<>"&"Target",B1:B1000,"<>"&"Sorted",C1:C1000,"Second",D1:D1000,"<>"&"Fail",E1:E1000,"<>"&"Fail",F1:F1000,"<>"&"Fail",G1:G1000,"<>"&"Fail",H1:H1000,"<>"&"Fail",I1:I1000,"<>"&"Fail")
Try this

**Deutz** · 06-26-2014, 11:44 PM

Thanks benishiryo and nflsales for your solutions which I have tested and work fine.

Sorry but I forget to include one extra column in the criteria. Managed to get it working in the COUNTIFS example but not in the first SUMPRODUCT solution where Fail can appear only once.

Need to add column U to the OR part.

So revised criteria is ...

Column A = "WP"
AND
Column B <> "Target" AND Column B <> "Sorted"
AND
Column C = "Second"
AND
(Column U = "Fail" OR Column D = "Fail" OR Column E = "Fail" OR Column F = "Fail" OR Column G = "Fail" OR Column H = "Fail" OR Column I = "Fail")