How to count checkboxes in Google Sheets

Google Sheets is a versatile, multipurpose spreadsheet application that organizes and manages information, data, and 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, you’re not alone.

In this article, you’ll see the formula you should use to total the number of checked cells in your spreadsheet, how to count the checked cells based on conditions, and how to convert that data into a pivot chart. Let us begin.

Wearing COUNTIF count checkboxes in Google Sheets

Each time a checkbox is checked, the cell value is set to “TRUE”. Unchecked cells are set to “FALSE”. Therefore, to count all checked cells, it will ask for the total number of “TRUE” cells in a range.

How to count marked boxes on sheets

Assume all checkboxes are within the range of cells A2 to A22. To count the number of checked boxes, do the following:

1. Click the cell in the spreadsheet where you want the total to display.
   
2. Then write”=COUNTIF” followed by the range of cells to check for a “true” value, for example, “(A2:A22, TRUE).” Your formula should look similar to the following: “=COUNTIF(A2:A22, TRUE).”
   
3. Press “Get into” to save the formula in the cell. The cell should now count the number of checked boxes (TRUE).
   

How to count unchecked boxes on sheets

To count unchecked boxes, the same process is used as for checked boxes, except that “TRUE” is changed to “FALSE.”

1. Select the cell where you want the count results to be displayed.
   
2. Enter the following formula using the range of cells you want:=COUNTIF(B2:B22, FALSE)“to count checkboxes that are not checked.
   
3. Press “Get into” to save the formula. Now the number of unchecked boxes appears.
   

How to count checkboxes based on conditions

Imagine that the data in your spreadsheet ranges from “Column B to Column D” and “Rows 3 to 17” and is configured as follows:

• Column B lists “department numbers”
• Column C lists the “tasks.”
• Column D contains the completed “check boxes”.

If you want to know the number of completed tasks (checked boxes) for a specific department, do the following:

1. Click the cell where you want the completed total count to appear.
   
2. Copy and paste the following string (don’t press Enter!):
   =COUNTIF(D2:D22, TRUE,B2:B22,E3)
   Note the plural form of “COUNTIF” in the formula.
   
3. Edit the ranges in the formula to suit your needs. Orange are the cells to check for completion, purple are the labels (departments) to associate with the orange cells, and light blue are the filtered results (Dept. 10) to check for TRUE values.
   
4. Press “Enter” if you haven’t already. The results should show the count of checked cells (completed tasks) for the “Dept. 10.”
   
5. To replicate the first formula cell (Dept. 10 group) for other groups (Dept. 12, 17, etc.), copy and paste it into the respective cells, one at a time. Google Sheets adjusts the formula automatically and intelligently.
   

The above formula does the following:

• Cells D3 through D17 are checked for “TRUE” values ​​(checked).
• The “TRUE” cells are associated with the specific department number between B3 and B17.
• The results are filtered, so only Dept. 10’s data counts and displays in cell F13.
• The ‘Dept. 10 formula cell (F13) is copied and pasted into other department formula cells (F14 to F17), and the formula automatically changes to the respective department (Dept. 12, 17, 24, or 30).

---

As you can see, Google Sheets can easily count and report checked or unchecked boxes/cells and even pide that data into subsections based on the conditions applied. The formulas in this article will help you sort or count items you’ve tagged based on their completion status, but feel free to expand them to other uses. You can use checkboxes for almost anything!

FAQ: Count Google Sheets Checkboxes

How do I make 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 runs from A to C, and from cell “2” to cell “22”, and is set up as follows:

· Column A lists the phases

· Column B lists the tasks, and

· Column C contains the check boxes

We will create a dynamic range chart to accommodate two additional columns and an unlimited number of 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 “Insert” and then “Chart.”

3. Through the “Chart Editor” in the “Data” tab, select the “Chart Type”, for example, “Column Chart”.

4. Make sure of the following:

· The “Use row 1 as labels” and “Change rows/columns” options are checked.

· The “Change rows/columns” option is not checked.

5. Under the “Horizontal Axis” option, select “Treat labels as text.”

How do I add custom values ​​to checkboxes?

1. Start your spreadsheet, then select the cells you want as checkboxes.

2. Select “Data” and then “Data Validation”.

3. Next to “Criteria”, select “Checkbox”.

4. Click on the “Use custom cell values” option.

5. Next to “Dialed,” enter a number. Optionally, enter a number next to “Unchecked.”

6. Next to “On invalid data”, select a validation option. To display a validation message every time you hover over the checkbox, choose “Show validation help text” next to “Appearance” and then add your message.

7. Select “Save”.