Google Sheets has been coming up with some really killer features lately, and the checkbox in Google Sheets is very useful.
It’s super easy to insert a checkbox to a cell (unlike MS Excel, where it’s still a bit tricky), and it allows you to do easily create an interactive checklist and dashboards.
The way Google Sheets handles checkboxes is different than Microsoft Excel. In Google Sheets, you can insert a checkbox within a cell – just like you put a number or a text string within a cell.
And because of this, if you want to sum or count the total number of checkboxes that have been checked, you can easily do that.
You can also count/sum checkboxes based on a condition. For example, count all the checkboxes for a specific month or for all activities completed in a specific phase of the project.
In this tutorial, I will show you how to easily count or sum the checkboxes in Google Sheets.
So let’s get started!
Count the Number of CheckBox in Google Sheets
When you insert a check box in Google Sheets, by default it can take two values – TRUE or FALSE.
When the checkbox is checked, the value of the cell becomes TRUE, and when the checkbox is unchecked, the value of the cell becomes FALSE.
So if you want to count all the checkboxes that are checked, you essentially need to count the total number of TRUEs in that range that has the checkboxes.
Count Checkboxes that are Checked
Suppose you have a data set as shown below, and you want to count the total number of cells which have a checkbox that is checked.
Below is the formula that can do that:
Since all the checkboxes would have the value true in the back end, I have used a COUNTIF function that only counts those cells that have the value TRUE in it.
Similarly, if you only want to count the total number of cells where the checkboxes are not checked (which in this case would give you the total number of tasks that are still pending), you can use the below formula:
Count Checkboxes Based on Condition
Extending the same concept, you can also count the number of checkboxes based on multiple conditions.
Suppose you have a data set as shown below where I have the tasks based on different phases in the project and I want to count the total number of checkboxes by phase.
Below is the formula that will give me the total number of checkboxes in Phase 1 that are checked (i.e., the task have been completed):
In the above formula, I checked for two conditions:
- Whether the checkbox is checked or not
- Whether the phase is phase one or not
Only and only if both of these conditions are met, then the instance is counted.
In our example above, it gives me 2 as there are two tasks in Phase 1 that has been completed.
Similarly, you can have multiple conditions in the COUNTIFS formula and count the number of checkboxes using it.
So this is how you can use a simple counter for the COUNTIFS function in Google Sheets to count the number of checkboxes.
I hope you found this tutorial useful.
Other Google Sheets tutorials you may also like: