Checkboxes are little square boxes inserted into cells on your worksheet. They are mostly used in timetables, dashboards, roasters, to-do lists, checklists and so on.
When you click a checkbox, a tick sign is inserted, which means the activity has been completed on your worksheet.
It also acts as a toggle, as the tick sign can also be removed by clicking on the checkbox.
When you have multiple checkboxes inserted on your worksheet, you want to count the checked and unchecked boxes in the array of checkboxes or you may want to count the checkboxes based on a condition.
In this guide, you would learn how to count checkboxes in Google Sheets.
Count the Number of Checkboxes In Google Sheets
An inserted checkbox takes two default values which are TRUE and FALSE. When a checkbox is ticked, it takes the TRUE value and when it’s unchecked, it takes the FALSE value.
So basically, when you want to count the checkboxes, all you need to do is count the number of TRUE and FALSE statements returned by the checkboxes on your worksheet.
How To Count Checkboxes that are Checked
In the sample worksheet below, we have an array of checkboxes in column A. Some are checked while others are unchecked.
To count these checkboxes, I would employ the COUNTIF function.
The COUNTIF formula used is=COUNTIF(A1:A10, TRUE).
Step 1: Select an empty cell and enter the above formula there. In this example, I selected cell B1.
Step 2: The COUNTIF function counts the cells with checkboxes which returns the TRUE statement.
Hit the Enter key and the total number of checkboxes is entered in the cell.
In case you want to count the unchecked cells, you would use the formula =COUNTIF(A1:A10, FALSE). The TRUE statement is replaced with FALSE.
Hit the Enter key and the total number of unchecked boxes is entered in cell B2.
Count Checkboxes Based On Condition
This is a more advanced way of counting checkboxes in Google Sheets. In the sample worksheet below, we have some tasks on the to-do list, the scheduled time and the checkboxes.
Let’s assume that the first task has been completed. So the formula used is =COUNTIFS(A2:A17, TRUE, C2:C17, D2)
The above formula would count the checkboxes based on two conditions. These conditions are if the boxes are checked and if they apply to the given task in cell D2.
The formula would return a value if the two conditions above are satisfied.
So we entered this formula in cell E2 and pressed the enter key.
The total number of checkboxes with the specified conditions is returned to the cell.
Frequently Asked Questions on Counting Checkboxes In Google Sheets (FAQs)
How do I make a chart with the data gathered from checkboxes in Google Sheets?
To answer this question, we would use the worksheet used earlier. This worksheet is a schedule that includes the checkboxes, time and task.
Step 1: Highlight the cell range which would be used for the chart. That is, A1:C17.
Step 2: Go to Insert on the toolbar and click on Chart.
Step 3: Open the Chart Editor and change the Chart type under the Setup tab. Here, I selected the Column Chart.
Step 4: Under the Customization tab, make sure the following features are adjusted.
- The “Use row 1 as labels” option is checked.
- The “Switch rows and columns” option is unchecked.
Step 5: In the Horizontal Axis section, select the “Treat labels as Text” option.
How do I add custom values to checkboxes?
To add custom values as checkboxes, you need to follow these steps below.
1. On a new worksheet, highlight the cells where you want the checkboxes inserted.
2. Go to the toolbar and select Data. Then, select Data Validation.
3. In the “Criteria” section, click on “Checkbox” from the drop-down list.
4. Select the “Use custom cell values” option.
5. In the Checked textbox, enter a number. You could also add a number under the unchecked textbox. This is optional.
6. In the “On invalid data” section, choose a validation.
7. To enter a validation message, under the “Show Validation Help text” section.
8. Click on Save.
Final Thoughts
These are the two methods used to count checkboxes in Google Sheets.
Now you know how to count checkboxes in Google Sheets. I hope you found this guide helpful. Thanks for reading.
