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

Topic Labels: Formulas
Solved
342 3
cancel
Showing results for
Did you mean:
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
18 - Pluto

Does this look right?

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

3 Replies 3
18 - Pluto

Does this look right?

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

4 - Data Explorer

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

12 - Earth

Hi,
``````LEN(CONCATENATE({Name},{Name 2},{Name 3},{Name 4},{Name 5}))-