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

0 votes

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.

Feb 24, 2023 in Others by Kithuzzz
• 38,010 points
387 views

1 answer to this question.

0 votes

You may try:

enter image description here

The formula in B1:

=MAP(A1:A12,LAMBDA(x,SUM(--ISNUMBER(SEARCH(", "&x&",",", "&E1:E11&",")))))
answered Feb 24, 2023 by narikkadan
• 63,420 points

Related Questions In Others

0 votes
1 answer

How can I sort one set of data to match another set of data in Excel?

In addition, INDEX MATCH is a more ...READ MORE

answered Oct 29, 2022 in Others by narikkadan
• 63,420 points
1,723 views
0 votes
1 answer

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

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

answered Nov 24, 2022 in Others by narikkadan
• 63,420 points
419 views
0 votes
1 answer

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

answered Dec 19, 2022 in Others by narikkadan
• 63,420 points
1,101 views
0 votes
1 answer
0 votes
1 answer

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

answered Oct 3, 2022 in Others by narikkadan
• 63,420 points
608 views
0 votes
1 answer

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

answered Oct 3, 2022 in Others by narikkadan
• 63,420 points
1,262 views
0 votes
1 answer

How to simplify adding multiple countifs formula in excel

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

answered Nov 26, 2022 in Others by narikkadan
• 63,420 points
376 views
0 votes
1 answer

Newbie needs Excel suggestions with a simple script

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

answered Feb 7, 2023 in Others by narikkadan
• 63,420 points
308 views
0 votes
1 answer

Excel - How can I get the average of cells where the value in one column is X and the value in another column is Y?

Use AVERAGEIFS ... =AVERAGEIFS(C2:C13,A2:A13,"Yellow Typ ...READ MORE

answered Nov 11, 2022 in Others by narikkadan
• 63,420 points
973 views
0 votes
1 answer

In a excel formula I need to create a list of names on one sheet based upon criteria/data of another sheet

The final formula is: =IF(ROWS($H$3:H3)<=$I$1,INDEX(Personnel! ...READ MORE

answered Nov 25, 2022 in Others by narikkadan
• 63,420 points
687 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP