Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Aug 06, 2019 03:24 PM
Hi, I’m attempting to create a file database and I’m running into an issue of how (if at all possible) calculate the number of times a certain set of values occur within different groups.
For example:
There are 4 intern options for high and college students
Opt. A
Opt. B
Opt. C
Opt. D
and I would like to calculate what percentage of 14-15 yrs are enrolled in each program.
Each record(row) has a field(column) for age and program.
In Google Sheets and Excel I can use COUNTIF, but what is the alternative in Airtable, if any?
Aug 07, 2019 06:01 AM
Assuming each student is linked to the intern option on another table, you can accomplish this with a conditional rollup.
Add a Formula Field to the table containing the ages of students. Let’s say we call it, {Age Count}, and use the formula…
IF(OR({Age} = 14, {Age} = 15), 1)
Next, on the table containing the options for internships, add a Count Field that counts the number of students assigned to the option. Let’s say it’s called {Student Count}.
Finally, add a Rollup Field that points at {Age Count} on the [Students] table, and use the formula…
SUM(values)/{Student Count}
Set the formatting to a percentage, and that should do it!
Aug 07, 2019 06:27 AM
Thank you so much for the quick and helpful reply! I attempted to integrate what you suggested and I’, not certain it worked. Although I did add
IF(OR({Age} = 14, {Age} = 15,{Age} = 16, {Age} = 17), 1)
I’m not sure if I’m able to do that without messing up what you gave me or if I have to add another or option…
I checked my airtable outcome with my Google Sheets outcome and they don’t match.
My goal is to make sure that each internship option is is fairly diverse age wise and want to check that each option has less than XX% ages 14-17 in each program.
Aug 07, 2019 06:32 AM
Actually, I think it worked! My google sheets was actually incorrect. Thank you so so so much! I’ve been searching all over the internet for the past week and a half! Huge weight lifted off my shoulders. Thank you so much.
Aug 07, 2019 01:11 PM
Yay!! You’re entirely welcome, happy to help :slightly_smiling_face: