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

Josh_Rutherford · ‎Nov 22, 2021

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:

Student (Text Strong) 1st Block Grade (Number) 2nd Block Grade (Number) 3rd Block Grade (Number) 4th Block Grade (Number) 5th Block Grade (Number)

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”))))

Justin_Barrett · ‎Nov 25, 2021

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:

Josh_Rutherford · ‎Dec 01, 2021

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())