I'm making a table to list the members of athletic teams, and I want to create a formula to determine if each team is male, female, or co-ed.

I know I can use =countifs(A:A,"A",B:B,"Male") to count how many males are in team A, but I'm lost on how to output that Team A is male, Team B is female, and Team C is co-ed.

::Edit::

My desired result would be an output that states Team A is male, Team B is female, and Team C is co-ed.

CodePudding user response：

the distribution can be achieved like this:

=QUERY(A:B, "select max(B) where B is not null group by B pivot A")

update

=QUERY(QUERY(
 {QUERY(FILTER(FILTER(A:A, B:B="male", B:B<>"female"), 
   NOT(COUNTIF(FILTER(A:A, B:B<>"male", B:B="female"), 
               FILTER(A:A, B:B="male", B:B<>"female")))),
 "select Col1,'Male',count(Col1) group by Col1");
  QUERY(FILTER(FILTER(A:A, B:B<>"male", B:B="female"), 
   NOT(COUNTIF(FILTER(A:A, B:B="male", B:B<>"female"), 
               FILTER(A:A, B:B<>"male", B:B="female")))),
 "select Col1,'Female',count(Col1) group by Col1");
 QUERY({FILTER(FILTER(A:A, B:B<>"male", B:B="female"), 
       COUNTIF(FILTER(A:A, B:B="male", B:B<>"female"), 
               FILTER(A:A, B:B<>"male", B:B="female")));
        FILTER(FILTER(A:A, B:B="male", B:B<>"female"), 
       COUNTIF(FILTER(A:A, B:B<>"male", B:B="female"),
               FILTER(A:A, B:B="male", B:B<>"female")))}, 
 "select Col1,'Co-Ed',count(Col1) group by Col1")}, "offset 1", ), "skipping 2", )

or:

=LAMBDA(x, y, QUERY(QUERY(
 {QUERY(FILTER(FILTER(x, y="male", y<>"female"), 
   NOT(COUNTIF(FILTER(x, y<>"male", y="female"), 
               FILTER(x, y="male", y<>"female")))),
 "select Col1,'Male',count(Col1) group by Col1");
  QUERY(FILTER(FILTER(x, y<>"male", y="female"), 
   NOT(COUNTIF(FILTER(x, y="male", y<>"female"), 
               FILTER(x, y<>"male", y="female")))),
 "select Col1,'Female',count(Col1) group by Col1");
 QUERY({FILTER(FILTER(x, y<>"male", y="female"), 
       COUNTIF(FILTER(x, y="male", y<>"female"), 
               FILTER(x, y<>"male", y="female")));
        FILTER(FILTER(x, y="male", y<>"female"), 
       COUNTIF(FILTER(x, y<>"male", y="female"),
               FILTER(x, y="male", y<>"female")))}, 
 "select Col1,'Co-Ed',count(Col1) group by Col1")}, 
 "offset 1", ), "skipping 2", ))(A:A, B:B)

CodePudding user response：

Use COUNTIFS to get the count of all males in each group. If it's

• 4, it's a all male group
• 0, it's all female group,
• anything else is a co-ed group.

This creates redundant groups for each row. Use UNIQUE to remove all redundant groups.

=ARRAYFORMULA(UNIQUE({A2:A13,SWITCH(COUNTIFS(A2:A13,A2:A13,B2:B13,"Male"),4,"Male",0,"Female","Co-ed")}))