Formula to return one of three values based on the contents of a range

Question

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.

Team Name	Gender
A	Male
A	Male
A	Male
A	Male
B	Female
B	Female
B	Female
B	Female
C	Male
C	Male
C	Female
C	Female

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.

Team	gender
A	Male
B	Female
C	Co-Ed

Answer 1

Supposing these 2 columns are A and B, you can add this to cell C1:

=UNIQUE(A:A)

This will populate the cells of column C with the unique values from column A.

For column D, there are 2 good options that work independent of how many team members are there. The first is to consider as "Male" when there is no "Female" for the group and vice versa, and "Co-Ed" otherwise. Add this formula to D1:

=IF(C1="","",IF(COUNTIFS(A:A, C1, B:B, "Male")=0, "Female", IF(COUNTIFS(A:A, C1, B:B, "Female")=0, "Male", "Co-Ed")))

Copy cell D1 to the rest of column D. Blank groups will have blank results also. The result will be like this:

A	B	C	D
A	Male	A	Male
A	Male	B	Female
A	Male	C	Co-Ed
A	Male
B	Female
B	Female
B	Female
B	Female
C	Male
C	Male
C	Female
C	Female

Another option is to take directly the gender that corresponds to the group, and "Co-Ed" if there's more than one. Add this to D1 and copy to the rest of the column:

=IF(C1="";"";IF(COUNTA(UNIQUE(FILTER(B:B;A:A=C1)))=1;UNIQUE(FILTER(B:B;A:A=C1));"Co-Ed"))

The result is the same as above (but easier to extend to other similar cases). The FILTER function brings the cells of the gender column based in the group column. UNIQUE reduces to different results. Hence, UNIQUE(FILTER(B:B;A:A=C1)) (varying C1 up to C3 or more automatically when copying) will result in Male for group A, Female for group B and 2 results (Male and Female) for group C. So we count the results with COUNTA: if only one, return it, if more, return "Co-Ed".

Answer 2

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

Group	Predominant type
A	Male
B	Female
C	Co-ed

Answer 3

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)