Create total count field from different multiple select fields if it meets certain criteria

[314BioSTL]

Hello - 

I am trying to get a total count field if student's have passed multiple tests. There are six fields representing each test. Within each multi select field the contents contain: Drop downs for fields:  Pass 4/5/24, Pass 4/17/24, Fail 4/5/24, Fail 4/17/24.  I want to use a multi-select tool so if/when they retake the test, I can note which date they passed or failed. 

Fields:

1. Test 1 
2. Test 2 
3. Test 3 
4. Test 4 
5. Test 5 
6. Test 6 

I would like another column to say, if the fields contain pass, then count it as one. If a student passes 4 out of 6 of the tests, I would like the column to have the total = 4. 

I am stuck combining the multiple fields. Airtable provides the following equation to count for one multi select field: IF({YOUR MULTIPLE-SELECT FIELD NAME},LEN({YOUR MULTIPLE-SELECT FIELD NAME})-LEN(SUBSTITUTE({YOUR MULTIPLE-SELECT FIELD NAME},",",""))+1)

Is there a way to create a formula that will count from multiple fields if specific criteria is met? 

Thank you in advance for any insight! 

[TheTimeSavingCo · Accepted solution]

Does this look right?

 

IF(
  FIND(
    'Pass',
    {Test 1}
  ),
  1
) +
IF(
  FIND(
    'Pass',
    {Test 2}
  ),
  1
)

 Link to base

[314BioSTL]

@TheTimeSavingCo This is exactly it and way simpler than I initially thought! Thank you so much for your quick reply. 

[Alexey_Gusev]

Hi,
Just for your info
The task 'How many times word "Pass" present in a list of fields" can be solved in a following way.

LEN(CONCATENATE({Name},{Name 2},{Name 3},{Name 4},{Name 5}))-
LEN(SUBSTITUTE(CONCATENATE({Name},{Name 2},{Name 3},{Name 4},{Name 5}),'Pass','Pas'))

each has it's pro and cons, so it depends on use case. 
Your statement 'If Pass present, count field as 1' closer to the first solution
Second has the flaw - You can add three Passes in 1st field and three Passes in the second, and formula will show 6. But in other cases it might be exactly the desired behavior