CODEWITHSUNDEEP
excelcountif
Kat
Kat

Reputation: 2498

Counting if multiple Columns has multiple criteria

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?

Upvotes: 0

Views: 42

Answers (1)

Scott Craner
Scott Craner

Reputation: 152505

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

Upvotes: 2

Related Questions