I'm trying to figure out how to calculate when, in a set of rows, any 2 of them match from 3 criteria.

So (6 isn't the correct answer, just an example), I would be trying to calculate how many of the rows contained 2 out of the three criteria: Cats, Dogs, Parrots. So each permutation is accounted for - Cats, Dogs, Lions would be valid, for example, but Cats, Hippos, Gazelle would not.

CodePudding user response：

try:

=ARRAYFORMULA(COUNTIF(LEN(SUBSTITUTE(FLATTEN(QUERY(TRANSPOSE(
 IFERROR((REGEXMATCH(FLATTEN(QUERY(TRANSPOSE(A2:C),,9^9)), 
 TRIM(SPLIT(E2, ",")))/1)^-1)),,9^9)), " ", )), ">=2"))