Skip to main content

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! 

Does this look right?

 

IF(

FIND(

'Pass',

{Test 1}

),

1

) +

IF(

FIND(

'Pass',

{Test 2}

),

1

)

 Link to base

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

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


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

Reply


Terms & Conditions