This website uses Cookies. Click Accept to agree to our website's cookie use as described in our Privacy Policy. Click Preferences to customize your cookie settings.

Turn on suggestions

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

Showing results for

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

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
773
12

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

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

- 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

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

Oct 11, 2021 05:24 PM

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

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

Oct 11, 2021 06:32 PM

i mean you can definitely set up 24 check boxes and double the formula

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

Oct 11, 2021 06:34 PM