Help

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

Topic Labels: Formulas
Solved
Jump to Solution
342 3
cancel
Showing results for 
Search instead for 
Did you mean: 
314BioSTL
4 - Data Explorer
4 - Data Explorer

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! 

1 Solution

Accepted Solutions
TheTimeSavingCo
18 - Pluto
18 - Pluto

Does this look right?
Screenshot 2024-05-03 at 10.51.31 AM.png

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

 Link to base

See Solution in Thread

3 Replies 3
TheTimeSavingCo
18 - Pluto
18 - Pluto

Does this look right?
Screenshot 2024-05-03 at 10.51.31 AM.png

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

 Link to base

314BioSTL
4 - Data Explorer
4 - Data Explorer

@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