How-To

How to Count Checkboxes in Google Sheets

how to count checkboxes in Google Sheets featured

With checkboxes in your spreadsheet, it’s not obvious how to count those that are checked. Here’s how to count checkboxes in Google Sheets.

Everyone loves a good checkbox. There’s something very satisfying about checking off a box once a job is complete and seeing your task list slowly get checked off.

Checkboxes aren’t just useful for lists, however. You can also use them to indicate selections, show availability, or a whole host of other reasons. If you’re creating a spreadsheet, adding checkboxes can be a useful visual indication to provide information about your data.

You can also use the values that checkboxes generate and use them in a formula, just like any other cell containing data. That allows you to count checked boxes, count unchecked boxes, and more. If you want to tally up your checkboxes, then here’s how to count checkboxes in Google Sheets.

How to Add Checkboxes to Google Sheets

Before you can count checkboxes in Google Sheets, you’ll need to add some to your spreadsheet.

Checkboxes are a useful way to give a visual indication of whether a task has been completed, or that a field has been selected. Adding them to your spreadsheet is easy to do.

To add checkboxes to Google Sheets:

  1. Open Google Sheets.
  2. Select the cells where you want your checkboxes to appear.
    google sheets selected cells
  3. Click the Insert menu.
    google sheets insert menu
  4. Select Checkbox.
    google sheets checkbox
  5. Checkboxes are added to your selected cells.
    google sheets checkboxes
  6. To check a box, click the checkbox.
    google sheets checked checkbox
  7. To uncheck a box, click a checked box again.
  8. You can check or uncheck as many checkboxes as you wish.
    google sheets checked checkboxes

How to Count Checked Checkboxes in Google Sheets

Once you’ve got checkboxes in your Google Sheets document, you may want to quickly see a total of how many of the boxes are checked. It’s possible to do because checking a box gives that cell a value of TRUE.

By counting how many of the cells have a value of TRUE, you can count how many of the checkboxes are checked.

To count checked checkboxes in Google Sheets:

  1. Click in a cell where you want the count of checked checkboxes to appear.
    google sheets select cell
  2. Type =COUNTIF(
    google sheets enter formula
  3. Select all of the checkboxes that you want to count.
    google sheets select range
  4. Type a comma, followed by TRUE)
    google sheets countif formula
  5. Press Enter and the formula will calculate, and you’ll see the total number of checked checkboxes.
    google sheets countif result

How to Count Unchecked Checkboxes in Google Sheets

It’s also possible to count the number of checkboxes that remain unchecked. This is useful if you want to count how many of your tasks or data fields have not yet been checked off.

Unchecked checkboxes have a cell value of FALSE. By counting the number of cells with the value of FALSE, you can see how many unchecked checkboxes remain.

To count unchecked checkboxes in Google Sheets:

  1. Select a cell where you want the count of unchecked checkboxes to appear.
    google sheets selected cell
  2. Type =COUNT(IF
    google sheets insert formula
  3. Select the cells containing your checkboxes, both checked and unchecked.
    google sheets select range
  4. Type a comma, followed by FALSE)
    google sheets countif formula
  5. Press Enter and the formula will calculate the number of unchecked checkboxes.
    google sheets countif result

How to Change the Default Values for Checked and Unchecked Checkboxes in Google Sheets

If your checkboxes are mixed in with other types of cells, which may also generate values of TRUE or FALSE, then the methods above will also count any cells that aren’t checkboxes, but still have a value of TRUE or FALSE. This will give an incorrect count of the number of checked or unchecked checkboxes.

If this is the case, it’s possible to set custom values for checked and unchecked cells, which ensures that you are only counting checkboxes and nothing else.

To use custom cell values for checkboxes in Google Sheets:

  1. Select the cells containing your checkboxes.
    google sheets selected cells
  2. Click the Data menu.
    google sheets data menu
  3. Select Data Validation.
    google sheets data validation
  4. In the panel to the right of the screen, click Add Rule.
    google sheets add rule
  5. Click the Criteria drop-down.
    google sheets criteria drop-down
  6. Select Checkbox.
    google sheets criteria checkbox
  7. Check the Use Custom Cell Values box.
    google sheets use custom cell values
  8. Enter the values that you want checked and unchecked cells to have.
    google sheets custom cell values
  9. Click Done at the bottom of the panel.
  10. Using the methods above, create a COUNTIF formula using your custom cell values in quotes instead of TRUE or FALSE.
    google sheets countif formula with custom values
  11. Your formula will now only count checkboxes, and nothing else.

Go Further With Google Sheets

Knowing how to count checkboxes in Google Sheets allows you to quickly tally up the checked or unchecked checkboxes in your spreadsheet. If your data contains a lot of checkboxes, this can be a significant time saver.

There are plenty of other useful tips and tricks to help you get even more out of Google Sheets. For instance, you may want to convert negative numbers to positive (and vice versa) in Google Sheets.

Click to comment

Leave a Reply

Your email address will not be published.

 

To Top