Excel Function to return yes / no if cell contains all instances of results-CodePudding

In search of an excel function to return a y/n answer if both men and women hold the same position

here's the problem.

there's an employee list with a columns for their "ID", "working title", "Genre" (this list only has 1 id per record, but the same working title (and gender) can appear for multiple individuals)

i need to have a column that returns true/false, or yes/no ... if a particular "working title" contains both the instance of having a "Male" and "female"

ex:

would show in a table with all working titles as:

CodePudding user response：

So, went with IF(), AND() and COUNTIFS() like so:

=IF(AND(COUNTIFS($B$2:$B$9,"M",$A$2:$A$9,C2),COUNTIFS($B$2:$B$9,"F",$A$2:$A$9,C2)),"Yes","No")

NOTE: None of the insert picture or other editing buttons are available as I enter this - not sure why. Now they are working - must be a glitch somewhere, here is the image I would have added earlier:

However, For my answer I had to assume that the working title is cell A1.