How to select Mode of data that is text in google sheets with criteria?

Question

I'm trying to find the mode of a data set given a certain criteria of employee population.

I've run into two problems and have been able to solve one.

Problem one was that the mode wasn't a value so i had to figure out how to have it return a text result.

Now, my problem is getting it to return the mode for the appropriate demographic (female, male, or non-binary). Right now, the formula is returning the mode for the entire data set and i cannot figure out how to get it to return for only one gender.

I've tried: =if(Data!C:C=B3, INDEX(Data!$B:$B,MATCH(MAX(COUNTIF(Data!$B:$B,Data!$B:$B)),COUNTIF(Data!$B:$B,Data!$B:$B),0)),"")

it returns the false value. I am just not sure how to get the criteria portion. Please help if you can!

Here's the link: https://docs.google.com/spreadsheets/d/12EHdL098jNEmtthDSirHR2byHxwkQWYwFP3f6X0G4uU/edit#gid=753302828

Answer 1

Here's a possible solution:

=INDEX(QUERY(Data!B:C,"select Col1, count(Col1) where Col2='"&B3&"' group by Col1 order by count(Col1) desc"),2,1)