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
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.

Feb 24, 2023 in Others 387 views

## 1 answer to this question.

You may try:

The formula in B1:

`=MAP(A1:A12,LAMBDA(x,SUM(--ISNUMBER(SEARCH(", "&x&",",", "&E1:E11&",")))))`
• 63,420 points

## Prompt message if Excel column has some value in asp.net c#

I believe it is wise to use ...READ MORE

## How do you populate a google sheets/excel column with cells from a column in another sheet in the same document?

You have two options on chronology: sheet-by-sheet =QUERY({Sheet1!X:Z; Sheet2!X:Z; ...READ MORE

## Excel COUNTIF "Column D" year equals 2015 and Column L equals "15 or greater"

COUNTIFS() with an S allows the ability of multiple ...READ MORE

## Excel Formula Query on Summing Annual Leave Days and Half Days

Try Something like =SUM(COUNTIF(\$A\$1:\$A\$8,"A"),COUNTIF(\$A\$1:\$A\$8,"HA")/2) - total count ...READ MORE

## How to simplify adding multiple countifs formula in excel

If the Excel version you are using ...READ MORE

## Newbie needs Excel suggestions with a simple script

Formula to Count the Number of Occurrences ...READ MORE