This post is going to show you all the ways to count how many checkboxes are checked in your Google Sheets workbook.

Checkboxes are a great way to indicate the simple status of an item in a list. For example, a to-do checklist is a convenient way of indicating which items you’ve done and which items you still need to do.

With each item represented by a checkbox, you may want to count the checked items in order to summarize your progress with the checklist.

Get your copy of the example workbook used in this post to follow along!

Default Checkbox Values

In Google Sheets, a checkbox can only have one of two possible values at any given time.

If a checkbox is checked, its value is TRUE. Otherwise, its value is FALSE.

When you first create a checkbox, it appears unchecked, meaning its default value is FALSE.

💡 Tip: You can view the underlying TRUE or FALSE value of a checkbox in the formula bar.

Select any cell containing a checkbox and observe its value will be shown in the formula bar at the top of the sheet.

The Checkbox is a special case of inserting a checkmark in your workbooks that allows you to toggle between two values.

Count Checkboxes with the COUNTIFS Function

One way to count checkboxes is by using the COUNTIF or COUNTIFS functions.

The COUNTIFS function conditionally counts cells within a selected range. More specifically, if a cell value meets your specified condition, then that cell gets included in the count.

For a range of checkboxes, you could count all the TRUE cell values and that would give you the number of checked items.

The above example shows five checkboxes, three of which are checked in cells B3, B5, and B6.

= COUNTIFS ( B2:B6, true )

Select an empty cell and paste the above formula into the formula bar, then press Enter.

The first argument is your entire range of checkboxes, whether checked or unchecked. The second argument is your condition for inclusion in the count.

Because you only want checked items counted, your condition is simply true. This will count each checked checkbox since their underlying value is TRUE.

As you can see above, the formula returns the correct value of 3 for the three checked checkboxes!

Count Checkboxes with the SUM Function

One clever way to count checkboxes is by using the SUM function.

This method is also a good way to introduce yourself to some of the more advanced concepts of Google Sheets.

= ARRAYFORMULA ( --B2:B6 )

The SUM function by itself can only sum numeric values, not TRUE and FALSE values.

So the first step is to convert the TRUE and FALSE values to one’s and zero’s values.

Select an empty cell and paste the above formula into the formula bar.

The sequence of hyphens or a double negative -- converts the checkbox values in cells B2 thru B6. TRUE will be converted to 1 and FALSE will be converted to 0.

By default, the hyphen sequence -- is designed to operate on only one cell, so you must use the ARRAYFORMULA function to allow the operation on a range of cells.

= SUM ( ARRAYFORMULA ( --B2:B6 ) )

Wrap the SUM function around the converted range, and it will sum 1 for each TRUE or checked item.

You can see the three checked checkboxes are being counted by the SUM method!

Count Checkboxes with the FILTER Function

When looking to count only checked checkboxes, the FILTER function method is a convenient choice because it takes advantage of the TRUE and FALSE nature common to both the FILTER function and the checkboxes.

You first filter the checkbox range to include only TRUE values, then you can use the COUNTA function to count the cells in that reduced range.

= FILTER ( B2:B6, B2:B6 )

Select an empty cell and paste the above formula into the formula bar.

This FILTER function examines the first argument, which is the entire checkbox range B2:B6.

The second argument is also the range of B2:B6. This may seem redundant to filter B2:B6 based on B2:B6, but it’s not.

This second argument is your condition that must be met for inclusion in the filter. The condition requires a true value in each cell that is to be included in the results.

Since the checked cells in B2:B6 are true, they are included in your filter.

= COUNTA ( FILTER ( B2:B6, B2:B6 ) )

Now that your filter contains only the checked checkboxes, you just need to count the number of filtered cells by wrapping a COUNTA function around your formula.

Press Enter to calculate the formula, and you’re done!

Count Checkboxes with the QUERY Function

The QUERY function is a powerful function that operates against a dataset and can return a summary similar to a pivot table.

The QUERY function relies on an SQL-like language and you can find more details about it in this guide to the QUERY function.

You can use the QUERY function to return only the checked items in your list and then count the results all within one formula!

= QUERY ( B2:B6, "SELECT COUNT(B) WHERE B = TRUE" , 0 )

Select an empty cell, then paste the above formula into the formula bar.

This code operates against all checkboxes in cells B2:B6, as indicated by the first argument.

The second argument is the query, written in the Google Visualization API Query Language.

The code portion WHERE B = TRUE filters column B cells for TRUE values only, meaning only checked checkboxes are returned.

The code portion SELECT COUNT(B) returns the count of the column B cells that you just filtered.

The third argument is an optional input to indicate the number of header rows in the data being queried. In this example, there are 0 header rows because the range B2:B6 contains all the data to be queried.

📝 Note: The QUERY function will return the count of checked checkboxes beneath a heading label of count!

Count Checkboxes with an Apps Script Custom Function

Google Apps Script, is the JavaScript based programming language designed to automate tasks in Google Sheets.

One feature Apps Scripts is the ability to create your own custom function. Your function can then be called from any cell on your sheet, just like any other built-in function.

You can use this feature to build a custom function that counts checks in a range.

Go to the Extensions menu and choose the Apps Script options to open the script editor.

function COUNTCHECKS(range) {
  var checkCount = range.reduce(function (a, b) {
    return a + (b[0] === true ? 1 : 0);
  }, 0);
  return checkCount
}

Along the left side, ensure the Editor icon is selected, then ensure the Code.gs code file is selected.

Paste the above code into the editor pane, as shown in the above screenshot.

This code creates the custom function named COUNTCHECKS, which requires one argument named range. This is range in which you want to count checkboxes.

A running total of checked checkboxes is maintained, then assigned to the checkCount variable.

The way checkCount gets calculated demonstrates the power of GAS from just a small piece of code.

The reduce method loops through all of the cells in the range. Within reduce, variable a tracks the running total of checked checkboxes, while variable b holds the checkbox value for the current iteration of the loop.

The code line return a + (b[0] === true ? 1 : 0) determines whether the current checkbox value is TRUE and if so, increments the running total by one.

= COUNTCHECKS ( B2:B6 )

To see your code in action, press the Save button in the code editor then go back to your sheet. Select an empty cell and paste the above formula into the formula bar then press Enter.

For checkboxes located in the range B2:B6, you can see the count of checked checkboxes in cell B8!

⚠️ Warning: This custom function will only work with a single column range input! It will only count checks in the first column of a multicolumn range.

Count Checkboxes with Filter Toggles

Adding data filter toggles is a popular way to perform data analysis in Google Sheets because you can quickly change your filtering conditions in a user-friendly way.

Go to your sheet and select the header cell of your checkbox column. In this example, the header cell is B1.

Go to the Data menu, then select the Create a filter option.

Your data filter is available to use now and you will see the filter toggle icon in the right side of each heading.

Now you can toggle your filter to view only the checked checkboxes.

Select the filter icon that has appeared in your heading cell. You will see your filtering possibilities for that column, which can only be TRUE or FALSE for checkboxes.

To view only checked checkboxes, deselect the FALSE value and ensure the TRUE value is selected. Then press the OK button.

Only the checked checkboxes are visible now.

You can then easily count these checkboxes without a formula. Simply select the checkboxes, then observe the count in the status bar.

The status bar is located in the bottom-right corner of spreadsheet footer.

=SUBTOTAL( 3 , $B$2:$B$6 )

If you would rather count the checkboxes with a formula, select an empty cell, then paste the above formula into the formula bar.

The SUBTOTAL function takes two arguments.

The first argument is a number representing your desired type of subtotal. In this example, the argument must be 3 because this value represents the COUNTA function and this is how it will return a count of checks.

The second argument is your entire range of checkboxes. In this example, the range B2:B6 contains the checks to count.

📝 Note: The SUBTOTAL function will count only the visible cells in the selected range.

You have now completed counting checked checkboxes with the filter toggles!

Count Checkboxes with Column Stats

The Column Stats feature is great for visualizing the distribution of your data in Google Sheets, all without occupying any additional cells.

Start by selecting any cell within the column of your checkboxes.

Go to the Data menu and choose the Column stats option.

This will open the Column stats pane on the right side of the workbook.

You can then find TRUE listed under the VALUE header, and observe its corresponding FREQUENCY value.

This FREQUENCY value is the count of checkboxes whose value is TRUE. In this example, that means we have 3 checked checkboxes!

Count Checkboxes with Explore

The Explore feature is handy when your sheet is structured like a table, as is often the case in Google Sheets.

This means if your first row contains your column headers and each column acts as a field of data, the Explore feature would be a good candidate for analyzing your sheet.

The Explore feature takes care of common tasks like column subtotaling.

You can use Explore to count checkboxes also. Go to your sheet and click the Explore icon located in the lower-right corner of the workbook.

This will open the Explore pane on the right side.

A bar chart has been created from the automated analysis of your sheet!

Notice how Status is being rightfully treated as a table field containing TRUE and FALSE data. Hover over the bar labelled TRUE to reveal the count of checked checkboxes!

Count Checkboxes with a Chart

For a relatively complex sheet of data, you can create your own chart to visualize your data in a customized fashion.

For a column of checkboxes, you can subtotal their checked and unchecked statuses by creating a custom column chart in just a few steps.

Select your entire column of checkboxes then go to the Insert menu and select the Chart option.

This will open the the Chart editor pane on the right. Ensure your desired Chart type is selected. In this example, the Chart type is a Column chart.

The chart has two columns, one labeled TRUE and the other labeled FALSE. These columns represent the count of checked and unchecked checkboxes, respectively.

Rather than reading from the heights of the columns, you can display the exact column counts by adding data labels.

Go to the Customize tab in the Chart editor pane and expand the Series section then check the Data labels option.

With the data labels now visible, scroll down a little and set the Data label font size to 30 for ease of reading.

Finally, scroll up a little,

💡 Tip: Expand the Chart & axis titles section and in the Title text, give the chart a more descriptive title like Count of Checkboxes.

You can now see separate counts for TRUE and FALSE values in the chart. These represent the checked and unchecked checkbox count, respectively.

Count Checkboxes with a Pivot Table

A pivot table is an important feature to master in Google Sheets since it can dynamically total, subtotal and filter different columns in a presentable format.

A column of checkboxes can be counted using a pivot table.

Select the range of your checkboxes along with your header cell. This range comprises cells B1:B6.

Go to the Insert menu and select the Pivot table option.

This will open the Create pivot table dialog, and you can choose a location for your pivot table.

Choose the Existing sheet option and select an empty cell. Press the Create button and you will get a new blank pivot table.

In the Pivot table editor pane that appears, go to the Rows section and click Add to select the Status column.

The dropdown item, Status, is available because you included that header cell in your selected range.

In the Values section, click its Add dropdown and select Status again.

This time, you are prompted by a Summarize by dropdown which should be set to COUNTA. The COUNTA function is perfectly suited to counting non-blank cells.

Your pivot table is now complete!

As you can see on your sheet, your pivot table rows enumerate all possibilities for Status. The COUNTA of Status column contains each count of checkboxes having the corresponding Status of TRUE or FALSE.

Conclusions

In this post, you’ve been introduced to many different ways to count checkboxes.

There are several formula options such as COUNTIF, SUM, FILTER, and QUERY that are effective.

You can also build your own custom COUNTCHECKS function to count checked items that can then be used like any other function. This results in a more simple formula input.

The filter toggle method is a good choice for on-the-fly counts that do not intrude on your sheet’s layout.

You can use the pivot table method if you have a lot of different levels of subtotals to calculate. Otherwise, the Column Stats, Explore, and Chart methods are a good way to visualize your counts.

Do you use any of these methods for counting checkboxes? Do you know any other interesting methods? Let me know in the comments below!