Upcoming database upgrades. Airtable functionality will be reduced for ~15 minutes at 06:00 UTC on Feb. 4 / 10:00 pm PT on Feb. 3. Learn more here

Count Number of Values that Fall within a Range in a Row

339 2
cancel
Showing results for
Did you mean:
5 - Automation Enthusiast

I use Airtable to help visualize and share data for our high school. Currently, I have a view that is set up with the following columns:

I added another column, a formula column, that was intended to display the number of classes each student was failing. The formula works at each level, but the formula itself is too long. Airtable truncates it. I am self-taught with regard to this sort of stuff, so there is probably a more elegant way to do this that fits within Airtable’s capabilities. I anticipate that someone will ask why I do not create a view and use the filter to achieve what I want, and my answer is that I would have to redo the filter every 9-weeks when new grades are being uploaded. With this formula, I can update the column names using Ctrl+F in Google Docs, then copy/paste it into the formula field. Any suggestions? Thank you!

I have pasted the formula I wrote below, with spaces to separate out each IF logical statement.

IF(AND({2nd 9 Grd 1}<70,{2nd 9 Grd 2}<70,{2nd 9 Grd 3}<70,{2nd 9 Grd 4}<70,{2nd 9 Grd 5}<70),“5”,

IF(OR(AND({2nd 9 Grd 1}<70,{2nd 9 Grd 2}<70,{2nd 9 Grd 3}<70,{2nd 9 Grd 4}<70),AND({2nd 9 Grd 1}<70,{2nd 9 Grd 2}<70,{2nd 9 Grd 3}<70,{2nd 9 Grd 5}<70),AND({2nd 9 Grd 1}<70,{2nd 9 Grd 3}<70,{2nd 9 Grd 4}<70,{2nd 9 Grd 5}<70),AND({2nd 9 Grd 2}<70,{2nd 9 Grd 3}<70,{2nd 9 Grd 4}<70,{2nd 9 Grd 5}<70)),“4”,

IF(OR(AND({2nd 9 Grd 1}<70,{2nd 9 Grd 2}<70,{2nd 9 Grd 3}<70),AND({2nd 9 Grd 1}<70,{2nd 9 Grd 3}<70,{2nd 9 Grd 4}<70),AND({2nd 9 Grd 1}<70,{2nd 9 Grd 4}<70,{2nd 9 Grd 5}<70),AND({2nd 9 Grd 2}<70,{2nd 9 Grd 3}<70,{2nd 9 Grd 4}<70),AND({2nd 9 Grd 2}<70,{2nd 9 Grd 4}<70,{2nd 9 Grd 5}<70),AND({2nd 9 Grd 3}<70,{2nd 9 Grd 4}<70,{2nd 9 Grd 5}<70)),“3”,

IF(OR(AND({2nd 9 Grd 1}<70,{2nd 9 Grd 2}<70),AND({2nd 9 Grd 1}<70,{2nd 9 Grd 3}<70),AND({2nd 9 Grd 1}<70,{2nd 9 Grd 4}<70),AND({2nd 9 Grd 1}<70,{2nd 9 Grd 5}<70),AND({2nd 9 Grd 2}<70,{2nd 9 Grd 3}<70),AND({2nd 9 Grd 2}<70,{2nd 9 Grd 4}<70),AND({2nd 9 Grd 2}<70,{2nd 9 Grd 5}<70),AND({2nd 9 Grd 3}<70,{2nd 9 Grd 4}<70),AND({2nd 9 Grd 3}<70,{2nd 9 Grd 5}<70),AND({2nd 9 Grd 4}<70,{2nd 9 Grd 5}<70)),“2”,

IF(OR({2nd 9 Grd 1}<70,{2nd 9 Grd 2}<70,{2nd 9 Grd 3}<70,{2nd 9 Grd 4}<70),{2nd 9 Grd 5}<70),“1”,“0”))))

2 Replies 2
18 - Pluto

Here’s the formula that I came up with:

({2nd 9 Grade 1} < 70) +
({2nd 9 Grade 2} < 70) +
({2nd 9 Grade 3} < 70) +
({2nd 9 Grade 4} < 70) +
({2nd 9 Grade 5} < 70)

Each expression in parentheses will return True or False, which Airtable then converts to 1 or 0. Each 1 then represents a grade below 70, and each 0 represents a grade of 71 or higher. Add them up and you how how may grades are failing grades.

I used the Batch Update app to populate fields with random numbers to test its behavior:

5 - Automation Enthusiast

Nice! Thank you so much! This is SO much better than the logic string I had to work out!! I had read the Airtable reference guide and alter this way to make it work in Airtable:

IF({2nd 9 Grd 1}<70,TRUE(),FALSE())+IF({2nd 9 Grd 2}<70,TRUE(),FALSE())+IF({2nd 9 Grd 3}<70,TRUE(),FALSE())+IF({2nd 9 Grd 4}<70,TRUE(),FALSE())+IF({2nd 9 Grd 5}<70,TRUE(),FALSE())