In Google Sheets, you can organize and manage your projects through online spreadsheets. The checkbox feature allows for interactivity, allowing users to check off completed items.
If you're trying to track team progress and want to know how to count the number of checked boxes in a spreadsheet, we're here to show you how.
In this article, we have included the formula to use to total the number of checked cells in your worksheet, how to count checked boxes based on conditions, and how to convert this data into a dynamic chart.
How to Count Checkboxes in Google Sheets
Each time a box is checked, the cell value is set to "true". Unchecked cells have the value “false”. Therefore, to count all checked cells, you will ask for the total number of "true" in a range of cells.
Suppose all of our checkboxes are in the cell range A2 to A22. To count the number of checked boxes:
- Click the cell in the worksheet where you want to display the total.
- Next, enter the equals sign (=), then the "COUNTIF" followed by the range of cells to check for a "true" value, eg A2:A22, TRUE.
- In total, your formula will look something like: =COUNTIF(A2:A22, TRUE).
How to count checked boxes
To count the number of cells set to true in a range of cells B2 through B22, follow these steps:
- Launch Google Sheets and open the spreadsheet.
- Click the cell where you want the total to appear.
- Type the "COUNTIF" function followed by the range of cells, for example =COUNTIF(B2:B22, TRUE).
- Press the Enter key. The total number of checked cells in your spreadsheet will be displayed.
To count the boxes that are not checked, enter the formula: =COUNTIF(B2:B22, FALSE).
How to count checkboxes based on conditions
Suppose the data in our project spreadsheet goes from A to C, and from cell 2 to cell 22, and is configured as follows:
- Column B lists the phases
- Column C lists the tasks, and
- Column D contains the checkboxes
We want to know the number of boxes checked in phase two. So do the following:
- Click the cell where you want the total to appear.
- Now enter, “=COUNTIFS(D2:D22, TRUE,B2:B22,E3).“
This formula checks for the following:
- Whether a cell is checked or not.
- Whether the phase is phase two or not.
Additional FAQs
How to create a chart with data collected from checkboxes in Google Sheets?
Google Sheets can automatically recognize new rows added to a chart area and update a chart accordingly with dynamic ranges.
Suppose the data in our project spreadsheet goes from A to C, and from cell 2 to cell 22, and is configured as follows:
Column A lists the phases
Column B lists the tasks, and
Column C contains the checkboxes
We'll create a dynamic range chart to accommodate two additional columns and unlimited rows. In this scenario, the data range would be A1 to E.
1. Select the cell range of your data, for example A1:E.
2. Click on “Insert” then “Chart”.
3. Via the "Chart Editor" under the "Data" tab, select the "Chart type", for example "Column chart".
4. Make sure of the following:
· The options “Use row 1 as labels” and “Swap rows/columns” are checked.
· The “Toggle rows/columns” option is not checked.
5. Under the "Horizontal axis" option, select "Treat labels as text".
How to add custom values to checkboxes?
1. Launch your worksheet, then select the cells you want as checkboxes.
2. Select “Data” then “Data Validation”.
3. Next to "Criteria", select "Checkbox".
4. Click on the “Use custom cell values” option.
5. Next to "Controlled", enter a number.
· (Optional) Next to 'Unchecked', enter a number.
6. Next to "On invalid data", select a validation option.
· (Optional) To display a validation message whenever the box is hovered over, next to “Appearance,” choose “Show validation helper text,” then add your message.
7. Select "Save".
Google Verified Sheets
Google Sheets caters to collaborative work with its spreadsheet checkbox feature. This interactive checklist is helpful for your team to distinguish completed tasks within a project.
The dynamic charts feature tracks changing data brought to the spreadsheet, so the information displayed is always accurate.
Now that we've shown you the formulas to use to total the number of checked boxes in your spreadsheet, how to find a total based on a condition, and how to turn that information into a dynamic chart, were you able to find out what you needed to know from your spreadsheet? Do you use other features to help you analyze your data? Tell us about it in the comments section below.