Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Re: COUNTIF Alternatives

3388 0
cancel
Showing results for 
Search instead for 
Did you mean: 
CYD_APH
5 - Automation Enthusiast
5 - Automation Enthusiast

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?

4 Replies 4
AlliAlosa
10 - Mercury
10 - Mercury

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!

CYD_APH
5 - Automation Enthusiast
5 - Automation Enthusiast

@AlliAlosa

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.

CYD_APH
5 - Automation Enthusiast
5 - Automation Enthusiast

@AlliAlosa

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.

Yay!! You’re entirely welcome, happy to help :slightly_smiling_face: