Counting Checkbox fields with a Rollup

[Alyse_Middleton]

I am working on a project/task management setup - I was aiming to have a field that tracks how many tasks are still open on a project.

I have a project table and a task table and the tasks are connected to the projects. The tasks have the ability to be marked as complete with a checkbox. I was trying to figure out how to use a rollup field to count the amount of uncompleted tasks.

[Ptt_Pch]

Without knowing more about your table configuration and how the projects are linked to the tasks I may have a maybe too quick solution (well, at least a lead to follow)…

On your tasks table you could create a formula field with this formula
(I’ve called the checkbox field “Done” and the formula field “Pending”)

IF(Done=1,0,BLANK())

So when a task is marked as “done” it give the result “0” instead of “1”.
Then on your projects table, create the rollup field (not sur where you want to have it though) rolling up the “Pending” formula field in the task table with COUNT(values) as the aggregation function…

:slightly_smiling_face:

[W_Vann_Hall]

@Ptt_Pch is on the right track (and is correct about our needing to know a little more about your design to be able to answer with certainty).

Keeping in mind that one can only rollup values in a single field across multiple records, you could determine how many boxes are unchecked for a given field in this way:

1. Assuming linked-record field {Link to Box} linked to record in table [Box]
2. Assuming checkbox field in [Box] called {Box}
3. Use a rollup field
   1. based on the {Link to Box} link
   2. rolling up {Box} field
   3. using the following aggregation formula:
      COUNTALL(values)-SUM(values)

Since checked checkboxes are evaluated as 1s, that formula first counts how many linked checkboxes there are and then subtracts the number of checked ones, leaving you with how many remain unchecked.

[Alyse_Middleton]

@W_Vann_Hall That worked PERFECTLY!! Thank you!

[Joseph_Brundige]

Hello!

I am looking to do the same thing, but I can’t figure out how to get this to work without converting my Checkbox into a “Link to another Record” which makes it so that it can’t accept new entries. I have one table called “studio1” and a checkbox called “Seen”. I want to use Rollup to find out how many total records there are and subtract the number of checked records.

What I have done over and over is create a new table called “Seen_Checked” and link to the checkbox field in studio 1 called “Seen”. I am just left with an empty field at this point. Then when I try to Roll up the data, I choose my field in Seen Checked (which is empty) and paste in the formula and it returns 0 for every record. I attached some screenshots. Can anybody shine a light on what I am doing wrong? Sorry for the novice question. Thank you!!!

[Kamille_Parks]

Lookup and Rollup fields require the Link to Another Record field to be filled. Right now, your [Seen Checked] table has 3 records, but none of those three are linked to any records in the [studio1] table. With the method you’re using (using a separate table to summarize data in another table), your [Seen Checked] table only needs one record, and that single record should be linked to every single record in [studio1].

Also for simplicity, instead of using a Rollup field you could use a Count field with “Only include linked records from the studio1 table that meet certain conditions” toggle on, and the condition being {Seen} is unchecked.

[Joseph_Brundige]

Thank you so much Kamille for your response! Can you please check my updated screenshots?

[Joseph_Brundige]

one more @Kamille_Parks !

[Kamille_Parks]

Kamille_Parks:

Lookup and Rollup fields require the Link to Another Record field to be filled.

Your Link field, now called {One Record} in the [Seen_Checked] table is still empty. You have to link all 7 records in [studio1] to your single record in [Seen_Checked].

Your base should look like this:

Airtable

Joseph_Brundige - Airtable

Explore the "Joseph_Brundige" base on Airtable.

[Joseph_Brundige]

Thank you @Kamille_Parks! I really appreciate you walking me through this. I understand now how to add records to the linked field. My confusion was I thought the records would populate automatically to my “Seen_Checked” table. Is there a way to get records into the linked table without clicking the “+” and adding them? Thank you!