Looking for a way to count cells in a range CONTAINING the word "Transgender" but while ignoring cells that only contain the word "Transgender Man" or "Transgender Women". Cells CAN contain "Transgender" and "Transgender Man" separated by commas and that's worth still counting. But if a cell contains "Transgender Man" but NOT "Transgender" then that needs to be ignored. If a cell only says "Transgender" and does not contain "Transgender Man" then that needs to be counted.
The problem is that my formula:
countIFS($P$3:$P, "*Transgender*")
also counts cells that only contain "Transgender Man" or "Transgender Woman" when I don't want it to.
Another approach could be to use QUERY(), something like:
=Query(P3:P,"Select Count(P) where P matches '(?:^|.*,\s*)Transgender(?:\s*,.*|$)' label count(P) ''")