Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

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

- Airtable Community
- Discussions
- Ask A Question
- Other questions
- Re: Calculations based on checkboxes (monthly paym...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

0
2337
0

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Comment Post Options

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Oct 11, 2021 01:44 PM

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.

Google “Remaining” formula:

```
=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

Extensions: 1/1 (TinyPNG Compression Script, looking for free alternatives)

Records:

Automation Runs: ??/100

Extensions: 1/1 (TinyPNG Compression Script, looking for free alternatives)

12 Replies 12

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Oct 11, 2021 03:00 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Oct 11, 2021 03:19 PM

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

Extensions: 1/1 (TinyPNG Compression Script, looking for free alternatives)

Records:

Automation Runs: ??/100

Extensions: 1/1 (TinyPNG Compression Script, looking for free alternatives)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Oct 11, 2021 04:24 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Oct 11, 2021 04:25 PM

@Kamille_Parks @kuovonne thoughts?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Oct 11, 2021 04:54 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Oct 11, 2021 04:56 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Oct 11, 2021 04:58 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Oct 11, 2021 05:00 PM

Ah, I thought that was its own row.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Oct 11, 2021 05:14 PM