Who are you, my participants were asked. and asked to check the boxes that apply to them. This is my list of potential responses:
Teacher
Teaching Assistant
SENCO
School Leader
Educational Psychology
Lunchtime Supervisor
School staff other
Parent
Researcher
Student
Person with neurodevelopmental condition
Other
As a result, some participants give multiple-answer responses, such as "Teacher, Parent" or "School staff other, Other."
For participants who provided a response to a certain question, I want to tally the number of each type of response. This means that I can't just create a formula that checks whether a cell meets a set of criteria; it must check that a cell includes text, be case-sensitive, and also satisfy the requirements of another column.
In order to accomplish this, I utilised the following formula, where R24 is the cell containing the word "Teacher," and the last half of the formula only includes individuals who typed a 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
The additional requirement 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, is something I am unable to add. (See here for an example of dummy data including a column of nonsense acting as an answer to the question, ignore% on left.) You'll note that my algorithm for "Other" counts answers containing "School staff other" even though COUNTIFS does not consider the case.
