# Calculations based on checkboxes (monthly payment tracker)

1502 12
cancel
Showing results for
Did you mean:
7 - App Architect

So I have a payment tracker in google sheets and am trying to figure out how I can get it to work similarly in Airtable. Below is an image of my google sheet. Everything in red has a formula.

The “Monthly” is calculated by dividing the total by the length.
The “Remaining” is calculated by multiplying the amount of the “Monthly” by the number of checkboxes, then subtracting that from the total.

``````=C3-(COUNTIF(E3:P3,TRUE)*D3)
``````
Airtable Account: Free Plan
Records: 1,200/1,200 1000/1000 (I might have to find a different solution soon)
Automation Runs: ??/100
12 Replies 12
9 - Sun

Im pretty sure theres ways you could restructure to do this more efficiently but if you’re dead set on using this workflow here’s how you do it.

Formula in ‘remaining’
TOTAL-SUM(IF(JAN,MONTHLY)+ IF(FEB,MONTHLY), IF(MAR,MONTHLY), IF(APR,MONTHLY), IF(MAY,MONTHLY), IF(JUN,MONTHLY), IF(JUL,MONTHLY), IF(AUG,MONTHLY), IF(SEP,MONTHLY), IF(OCT,MONTHLY), IF(NOV,MONTHLY), IF(DEC,MONTHLY))

Formula in ‘monthly’
TOTAL / LENGTH

7 - App Architect

I am all game for a more efficient way to do this, so long as it is easily readable at a glance. Especially when using a 24 month payment cycle.

Airtable Account: Free Plan
Records: 1,200/1,200 1000/1000 (I might have to find a different solution soon)
Automation Runs: ??/100

18 - Pluto

It looks like you have a total amount to pay, which is split into several monthly payments. Each checkbox represents a payment that needs to be paid. When the total is paid off, all the check boxes would be cleared.

There are many ways to represent this in Airtable. One way is to have two tables: one for [Accounts] and another for linked [Payments], with 1-24 linked payments, depending on the situation. On the other hand, this method might quickly run up the number of records.

16 - Uranus

Assuming you recreate this exact table structure in Airtable and use Cehckbox-type fields for all the months, the formula for the Remaining field would be:

``````Total - (SUM(JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DEC) * Monthly)
``````

I suspect, however, you may benefit from an alternate base structure since as far as I can tell you can only track one year (2021) at a time like this. If some of the lengths are 24, how would you even mark that they paid both March’s statement? Without more information on your use case I would imagine the preferred database-oriented approach would be to have a setup like the one advised by Kuovonne above.

18 - Pluto

I think this is why there are two rows of checkboxes for the row with 24 as the length. This couldn’t really be done horizontally in Airtable.

16 - Uranus

Ah, I thought that was its own row.

9 - Sun

in theory you could take the super long way and have all 24 check boxes horizontally and have a formula that formats rich text and pastes it into a rich text field to make it more easily readable but you would burn an automation for every little update. that’s also a bit more advanced lol I would try to write it just for the fun of it if interested lol

7 - App Architect

It is a layaway payment tracker. If the box is checked, that means a payment has been made and the monthly amount is deducted from the remaining total. Layaway lengths are 6,9,12, and 24 months.

The formula works fine, just the 24-month layaway will be a bit fiddly. Maybe I will stare at it later and see if I can come up with a new design. I also had a note on the google sheet with the transaction number for that payment (the little black triangles).

Airtable Account: Free Plan
Records: 1,200/1,200 1000/1000 (I might have to find a different solution soon)
Automation Runs: ??/100