Thread by @benlcollins

Let's learn how to use custom checkbox values to use checkboxes directly in #GoogleSheets formulas. 🎉

(1/6)

When you create a checkbox through the Data > Data validation menu, you have the option to set custom values, instead of TRUE/FALSE.

For example, you could set the checked value to 1, instead of TRUE, and the unchecked value to 0, instead of FALSE.

(2/6)

Using 1 and 0 like this allows you to use a SUM function to count how many checkboxes are checked.

This wouldn't work with TRUE/FALSE values, which would always sum to 0 whatever checkboxes were checked.

(3/6)

Here's a student assignment template.

It uses checkboxes with the TRUE value set to 20% and the FALSE value to 0, to show overall progress.

The SUM function adds up the 5 boxes. Each checkbox has a value of 20% when checked, so if all 5 are checked the total is 100%.

(4/6)

Some notes...

1) While you can enter decimal values into the checkboxes, you can't enter values less than 1 apart. E.g. you can't enter 0.5 for TRUE and 0 for FALSE, but you can enter 1.5 and 0.

(5/6)

2) Formulas are not permitted in checkbox values.

3) You only need to set up your data validation checkbox for one cell. You can copy-paste it into other cells.

If you use this technique for anything interesting, let me know!

(6/6)