EXCEL: Count case sensitive criteria in one column if meets criteria in another column

My participants were asked 'who are you?' and asked to tick as many as applies to them. My list of possible answers are as follows:

Teacher
Teaching Assistant
SENCO
School Leader
Educational Psychology
Lunchtime Supervisor
School staff other
Parent
Researcher
Student
Person with neurodevelopmental condition
Other

This results in some participants producing responses with multiple answers like 'Teacher, Parent' or 'School staff other, Other'.

I want to count how many of each response there are for participants who answered a specific question. This means that I cannot simply write a formula that tests whether a cell = criteria, it must be that a cell contains the text, but be case specific, whilst also meeting criteria from another column.

I used the following formula to do this (R24 in this case referring to cell containing the word 'Teacher' and the latter part of the formula only including those who typed something in response to the question I am interested in).

=COUNTIFS(Table1[[#All],[Who are you?]],"*"&R24&"*",Table1[[#All],[What was the most valuable thing that you learnt?]],"<>"&"")

This works fine for every response except 'Other'. As COUNTIFS is not case sensitive, the total produced for 'Other' includes that for those who also select 'School staff other'.

When trying to work this out, I can manage to produce a case sensitive total using the following formula (R35 being a cell containing the word 'Other' and the range being a set of dummy data like below):

=SUMPRODUCT(--(ISNUMBER(FIND(R35,R39:R49))))

Teacher, Parent
Teacher, Parent
Educational Psychology
SENCO, Other
Teacher
Other
School staff other
3
Other
SENCO, Other
School staff other, Other

However, I cannot find a way to add the additional criteria that this should only be counted for participants who answer the question i.e. do not have a blank cell in another column of data (see here for e.g. of dummy data including column of nonsense acting as answer to question, ignore % on left). You will see that my problem is that my formula for 'Other' counts the answer containing 'School staff other' as COUNTIFS is not case sensitive.

Please note that the example I give on the image is all on one worksheet whereas the formula needs to pull the data from a large dataset from other worksheets.

You may try:

Formula in B1:

=MAP(A1:A12,LAMBDA(x,SUM(--ISNUMBER(SEARCH(", "&x&",",", "&E1:E11&",")))))

If not supported, just scratch the iterative MAP(), but the main idea here is that you are using the fact that multiple answers are comma-space delimited. Use this to your advantage.