Help

Aggregate count of checkboxes

Solved
Jump to Solution
5452 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Matthew_Lee
4 - Data Explorer
4 - Data Explorer

Hello everyone,

I am an Airtable newbie, so I apologize if this has been asked before, should be obvious, etc. I am currently working on a table where we have 6 checkbox fields for each field record. If I have 10 records, there are a potential of 60 checkboxes that can be checked. I am trying to find a way to summarize how many of the 60 checkboxes have been checked (ideally by percentage, but number would work too). Does anyone have a suggestion on how I might be able to do this?

Thanks and I hope everyone has a great day!

-M

1 Solution

Accepted Solutions
Justin_Barrett
18 - Pluto
18 - Pluto

Welcome to the community, Matthew! :grinning_face_with_big_eyes: One thing to keep in mind is that Airtable’s formulas, which you’ll use to check each checkbox state, operate only with what’s in the same record. So in your case, where you have six checkbox fields in a given record, the formula you write will only tally the state of those six. Unlike with a spreadsheet, Airtable formulas can’t “see” field values in other records. To create an aggregate summary across all ten records will require linking to another table.

Here’s an example. I’ve got ten records with six checkboxes each, with the fields named {Ck1} through {Ck6}.

Screen Shot 2019-08-13 at 7.57.37 PM.png

The last field is a formula that concatenates the string equivalents of all the checkboxes (a check returns “TRUE”, while no check returns nothing), uses SUBSTITUTE to turn each “TRUE” into “T”, then counts the string length.

LEN(SUBSTITUTE(Ck1 & Ck2 & Ck3 & Ck4 & Ck5 & Ck6, "TRUE", "T"))

I’ll add a link field at the end, with all records pointing to the same record in another table:

38%20PM

In that summary record, I’ll roll up the sum of all values in the {Checked} field from my main table:

Screen Shot 2019-08-13 at 8.11.50 PM.png

Next I’ll add a Count field type to count the linked records, then use that in a formula to determine the final percentage:

46%20PM

Screen Shot 2019-08-13 at 8.13.10 PM.png

If I add more records to the main table, that summary percentage will update appropriately as long as the link to the summary record exists. This can be automatically created by grouping the main table by that link field, so that all new records get auto-linked.

See Solution in Thread

2 Replies 2
Justin_Barrett
18 - Pluto
18 - Pluto

Welcome to the community, Matthew! :grinning_face_with_big_eyes: One thing to keep in mind is that Airtable’s formulas, which you’ll use to check each checkbox state, operate only with what’s in the same record. So in your case, where you have six checkbox fields in a given record, the formula you write will only tally the state of those six. Unlike with a spreadsheet, Airtable formulas can’t “see” field values in other records. To create an aggregate summary across all ten records will require linking to another table.

Here’s an example. I’ve got ten records with six checkboxes each, with the fields named {Ck1} through {Ck6}.

Screen Shot 2019-08-13 at 7.57.37 PM.png

The last field is a formula that concatenates the string equivalents of all the checkboxes (a check returns “TRUE”, while no check returns nothing), uses SUBSTITUTE to turn each “TRUE” into “T”, then counts the string length.

LEN(SUBSTITUTE(Ck1 & Ck2 & Ck3 & Ck4 & Ck5 & Ck6, "TRUE", "T"))

I’ll add a link field at the end, with all records pointing to the same record in another table:

38%20PM

In that summary record, I’ll roll up the sum of all values in the {Checked} field from my main table:

Screen Shot 2019-08-13 at 8.11.50 PM.png

Next I’ll add a Count field type to count the linked records, then use that in a formula to determine the final percentage:

46%20PM

Screen Shot 2019-08-13 at 8.13.10 PM.png

If I add more records to the main table, that summary percentage will update appropriately as long as the link to the summary record exists. This can be automatically created by grouping the main table by that link field, so that all new records get auto-linked.

Matthew_Lee
4 - Data Explorer
4 - Data Explorer

Thank you Justin! That worked perfectly. I had to spend a little time figuring out how to properly reference columns (I was using quotes instead of curly brackets), but after that everything you said worked great! I appreciate your help!