countif formula not working with text

[fgbdrum]

hello-

my countif formula seems to work when the cell is included in the criteria contains number but not when they contain text. i have looked for extra spaces before and after the text in question and there are no rogue spaces. i've used the trim formula as well as the clean formula to make sure.

there are 2 files:

1. ORIGINAL Proposed Group Breakdown - KPI Dashboards2020.xlsx
2. Headcount for Dashboard Nov.xlsx

the first file looks up to the second file in the formulas.

focusing only on formula in cell C4 of the 1st file, i am trying to count the number of males.

=COUNT(IF(FIND($A4,'[Headcount for Dashboard Nov.xlsx]KPI Healthcare Headcounts 1 '!$O$1:$O$17649,1)=1,IF('[Headcount for Dashboard Nov.xlsx]KPI Healthcare Headcounts 1 '!$G$2:$G$17649="*"&C$2&"*",'[Headcount for Dashboard Nov.xlsx]KPI Healthcare Headcounts 1 '!$E$2:$E$17649)))

it's returning the number 1, can't figure out why it's returning that.

if someone could help me figure out why this formula isn't working, i would most appreciate it. thank you.