0

I have three columns, A, B, C. They can all have three different values.

I want to count the row if all three columns contain "x" OR "y" in each of the column. For example {x,y,x} is counted, but {x,t,x} is not counted. If one of the three doesn't have either of these values, don't count the row.

I can get the basics for if I want to count the column if it has x or y, 

=COUNTIF('A:A,"x") + COUNTIF('A:A,"y")

But when I try to go across all three columns I start getting issues. 

=COUNTIFS(A:A, {"x","y"}, B:B. {"x","y"}, C:C, {"x","y"})

This doesn't seem to get an "OR" but an "AND". What am I doing wrong?

Kat
  • 2,460
  • 2
  • 36
  • 70

1 Answers1

2

For this I like using SUMPRODUCT, though it is an array formula and full column references should not be used:

=SUMPRODUCT(((($A$1:$A$1000<>"x")*($A$1:$A$1000<>"y"))+(($B$1:$B$1000<>"x")*($B$1:$B$1000<>"y"))+(($C$1:$C$1000<>"x")*($C$1:$C$1000<>"y"))=0)*1)

enter image description here

Scott Craner
  • 148,073
  • 10
  • 49
  • 81