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

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β))))

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:

1 Like

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

This topic was solved and automatically closed 15 days after the last reply. New replies are no longer allowed.