Google Sheets has been coming up with some really killer features lately, and the checkbox in Google Sheets is very useful. And you can even count checkboxes in Google Sheets!
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 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 use a Google spreadsheet count checkbox function.
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 count checkboxes in Google Sheets using COUNT or SUM functions.
Let’s get started!
Table of Contents
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.
We can use this logic to count if a checkbox is checked Google Sheets and vice versa.
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. We usually use the COUNTIF formula to count checkboxes in Google Sheets.
Below is the formula that can do that:
=COUNTIF(B2:B11,TRUE)
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 to make Google Sheets count checked checkboxes.
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:
=COUNTIF(B2:B11,FALSE)
Count Checkboxes Based on Condition
Extending the same concept, you can also make Google Sheets count checkboxes based on multiple conditions. All you need to do is add ifs to the COUNTIF checkbox Google Sheets function.
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):
=COUNTIFS(C2:C11,TRUE,A2:A11,E2)
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.
How to Add Custom Values to Checkboxes.
Google Sheets also lets you assign custom values to your checkboxes instead of the true and false values. Here’s how you do it:
- In the toolbox, go to Data > Data validation
- In the data validation window, go to the criteria drop down menu and select checkbox
- Select use custom values
- Add the values which you wish to assign to your checkbox. In this case, for checked we chose “yes” and for unchecked “no”.
- Click save
This will automatically insert checkboxes. Now, if you check a box, it will show the assigned custom value in the formula bar.
Count Checkboxes When Using Validation.
If you used custom values for your check boxes then you can’t use the above COUNTIF formula to find the SUM of checkboxes in Google Sheets. You would have to change it to match the custom values to learn how to count check marks in Google Sheets. For example, if you used yes for checked and no for unchecked, then you would use the following formula to count the number of checked boxes:
=COUNTIF(B2:B11,YES)
If you want to count the number of unchecked boxes then you will need to replace the “Yes” with “No” in the formula.
How to Make a Chart with the Data from Checkboxes in Google Sheets.
After counting checkboxes in Google Sheets you can also create a chart
- Select the cell range of data you want to use
- Go to Insert then chart
At first, your chart will show no data like below:
- In the chart editor window, check Use 1 row as headers and ensure that the switch rows/columns option is unchecked.
- Ensure that the x-axis is treated as a label.
- Go to chart type and select the chart type you wish to use
This will give you a visual representation of the number of checkboxes that are checked and unchecked.
Conclusion
So this is how to SUM checkboxes in Google Sheets to find the number of checked and unchecked checkboxes by using a simple counter for the COUNTIFS function in Google Sheets.
I hope you found this tutorial useful.
Other Google Sheets tutorials you may also like:
