-3

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

enter image description here

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.

player0
  • 124,011
  • 12
  • 67
  • 124
mubambi
  • 39
  • 3
  • What do you mean by 2 out of three criteria? Does it mean from the Combination Cats, Dogs, Parrots, it can be either Cats, Dogs || Cats, Parrots || Dogs, Parrots and it will count it? Also please share an example or copy or your spreadsheet, remove sensitive data if any. – Logan Sep 13 '22 at 05:15
  • I think I understand the goal. But I would also request that you share a link to a spreadsheet with the data already entered, keeping in mind that this is a free, volunteer-run site. Providing the volunteer contributors with live, usable data around which to build and test formulas will increase your chances of engagement from this community. In addition, please explain the purpose of the Row-1 entries shown in your post. I see no obvious connection to the goal. – Erik Tyler Sep 13 '22 at 06:52

1 Answers1

2

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"))

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124
  • This solved it! Thanks so much. Is there any way to also have the formula add matching values, say, from the D column? I've taken the other commenters advice and published my exact sheet here: https://docs.google.com/spreadsheets/d/e/2PACX-1vSzA_2EIW8La4_2Z--d50fO6rwJAv2K3QTlkFUb43GO2DPDy9oAWobMPYpP06BqX-rnwBO87P1olwNG/pubhtml?gid=1549824921&single=true What I want to do is add the values in the "Gains" column if two out of the three preceding columns match the column to the right. – mubambi Sep 13 '22 at 14:35
  • 1
    @mubambi if you want to add D column you just need to change A2:C to A2:D – player0 Sep 13 '22 at 16:18
  • Sorry @player0, when I said add, I meant sum, not include – mubambi Sep 13 '22 at 16:48
  • As in, if column D had numbers, how would I get the sum of all the D values for which A:C matched 2 of the three E values? – mubambi Sep 13 '22 at 23:47
  • 1
    @mubambi https://i.stack.imgur.com/4V5IJ.png – player0 Sep 14 '22 at 00:48