Tally and display multiple dropdown fields in one field

[airtablenewbie]

Total airtable newbie here in need of some help and direction, please! I have two tasks I would like to accomplish

Please find below a screenshot of what my table looks like. I have a drop-down selection field for each year camp has taken place, and can select “camper” “staff” or “canceled” for each year.

 

Task 1: I would like to have a field that calculates the number of years a person has been in attendance at camp, whether or not they were staff or a camper. So the year tallies would look like this:

Person A Years at camp = 1 

Person B Years at camp= 2

Person C Years at camp= 1 (Because they canceled in 05) 

What are the steps I need to do this? I think part of doing this airtable wizardry will involve making my year fields linked record fields- do I link all these year fields together and if so, how? Once my fields are set up the way they need to be, how do I create a formula that basically says if year= camper or year=staff, either equals “1” or whatever and then counts those, but doesn’t count blank spaces or if year=canceled?

Task 2:

If I then wanted a field that displayed all the values across the record restated, separated by semicolons or something, in a single field, how would I do that? I phrased that confusingly, but what I’m looking for is a field that would say something like “camp career status” and would look like this:

Camper     Camper Career Status

Person A  "‘05 staff"

Person B  "‘05 camper; ‘06 staff"

Person C  "‘06 staff"

How would I go about doing that?

Thank you in advance so much for your help!

[Ella]

Hi @airtablenewbie ,

From your explanation, I think I had to do something similar to task 1. 

I used a formula field with an If() and +

For example:

(IF({2005 Status}='Some Value', 1, 0))

+

(IF({2006 Status}= 'Other Value', 1, 0))

+

etc

NB: You might need (I am not sure) to add an OR() statement to the Some Value section if there are multiple acceptable values but I have not tried this part yet.

For Task 2 maybe try a second formula field with a concatenate formula to add up all the values you need or just use the & symbol in between all the field values you need and add punctuation with ' '.