Count Checkboxes in Google Sheets with COUNTIF

To count checked checkboxes in Google Sheets, use COUNTIF with TRUE as the criterion. If the checkboxes are in B2:B100, enter =COUNTIF(B2:B100,TRUE) in an empty cell; the result is the number of checked boxes in that range.

The quickest way to count checked boxes

Google Sheets checkboxes normally store a Boolean value: a checked box is TRUE, and an unchecked box is FALSE. The count formula looks for the checked value, rather than counting the checkbox symbols or the cells that contain them.

For a task list with checkbox cells in B2:B11, use this layout and formula:

Assuming B2:B11 contains the checkboxes, enter =COUNTIF(B2:B11,TRUE). If six of those boxes are checked, the result is 6.

Put the formula outside the checkbox range so it does not overwrite a checkbox or become part of the range being counted. You can place it beside a heading such as “Completed” or in a summary area above the task list. When someone checks or unchecks a box, the result updates automatically.

How Google Sheets checkboxes work

A checkbox is a cell value with a visual control attached to it. It is not just a drawing on top of the grid. That is why ordinary counting functions can evaluate the cell contents, and why changing a checkbox changes a formula result.

To add standard checkboxes in a browser, select the cells and choose Insert > Checkbox. A newly inserted checkbox is unchecked by default. In a normal checkbox range, checking one sets its cell to TRUE; clearing it sets the cell to FALSE. A formula can therefore count checked items without any additional setup.

For example, a household checklist might have item names in A2:A20 and checkboxes in B2:B20. The formula counts completion states in column B; it does not count the text in column A. Keeping task names and checkboxes in separate columns also makes it easier to filter, sort, and summarize the list.

Count unchecked boxes without counting empty rows

To count unchecked standard checkboxes, use FALSE as the criterion. If every cell in the selected checkbox range contains a checkbox, the count is straightforward:

Assuming B2:B100 contains only checkbox cells, use =COUNTIF(B2:B100,FALSE). If 12 boxes are unchecked, the result is 12.

A practical sheet may have unused rows in the checkbox column, however. In that case, count only rows that also have a task name. With task names in A2:A100 and checkboxes in B2:B100, use =COUNTIFS(B2:B100,FALSE,A2:A100,"<>"). This counts unchecked boxes only when the corresponding task-name cell is not empty. The "<>" criterion means “not equal to blank.”

This distinction matters because a blank row is not necessarily an unchecked task. Decide what should qualify as a real item, then add a condition for that row—for example, a nonblank task name or an assigned person. That gives a more meaningful count than treating every unused cell as work left to do.

Count completion by category or other conditions

COUNTIFS counts rows that meet more than one condition. It is useful when a single total is not enough, such as when a project manager wants to know how many urgent tasks are complete. Each criteria range should cover the same rows as the checkbox range.

Assuming task names are in A2:A100, checkboxes are in B2:B100, and priority labels are in C2:C100, use =COUNTIFS(B2:B100,TRUE,C2:C100,"Urgent"). This counts rows where the box is checked and the priority is Urgent. It does not count unchecked urgent tasks.

You can change the second condition to a different category, owner, or status column. For example, a volunteer coordinator could count checked rows assigned to one person; a teacher could count submitted assignments for one class. Make sure each row represents the same record across the columns, or the result may combine unrelated values.

Calculate a completion percentage

A count tells you how many tasks are done, while a percentage compares completed tasks with the number of listed tasks. For an accurate percentage, use the task-name column as the denominator so blank rows do not inflate the total.

Assuming task names are in A2:A11 and their checkboxes are in B2:B11, use =COUNTIF(B2:B11,TRUE)/COUNTA(A2:A11). If there are 10 named tasks and six checked boxes, the formula returns 0.6, or 60% when the result cell is formatted as a percentage. In Google Sheets, use Format > Number > Percent to display it as a percentage.

If there are no task names, the denominator is zero, so the division cannot produce a meaningful percentage. You can prevent an error by checking that the list has at least one item first, or by using IFERROR to display a chosen fallback.

Assuming the same layout, use =IFERROR(COUNTIF(B2:B11,TRUE)/COUNTA(A2:A11),0). With no named tasks, it returns 0; otherwise it returns the completed fraction. Format the result as a percentage if that is easier to read.

Use custom checkbox values

Google Sheets also lets you assign custom values to a checkbox. This can be useful when another formula or connected workflow expects labels such as “Done” and “Not done,” rather than the standard Boolean values. To configure the values, select the cells and open Data > Data validation, then choose the checkbox criteria and set the custom checked and unchecked values in the validation options.

Once custom values are set, use the checked value in the counting criterion. For instance, if the checked value is the text Done and the checkbox cells are B2:B100, use =COUNTIF(B2:B100,"Done"). If the custom checked value is 1 instead, use =COUNTIF(B2:B100,1). The criterion must match the stored value, not merely the label you expect to see.

If a formula that previously worked with TRUE stops giving the expected result after someone changes the checkbox settings, check the validation rule first. A standard checkbox and a checkbox with custom values do not store the same checked and unchecked values. Updating the formula to match the actual values is usually the fix.

Choose a reliable range for the count

A formula with a fixed range, such as B2:B100, is easy to understand and excludes the header. Its limitation is that new rows below row 100 will not be counted until the formula range is extended. Choose a range large enough for the expected list, and remember to adjust it if the list grows beyond that point.

For a list that will keep growing, a whole-column reference can avoid frequent range edits. Assuming checkboxes are in column B and the heading is in row 1, use =COUNTIF(B2:B,TRUE). Starting at row 2 keeps the header out of the range. A bounded range can still be preferable in a very large or complex spreadsheet because it makes the intended data area clearer.

Use the same endpoints in related formulas. If your checkbox count covers rows 2 through 100 but your task-name count covers rows 2 through 90, the totals describe different sets of records. Matching ranges help prevent confusing completion percentages and category counts.

Common counting problems and fixes

The result is zero even though boxes look checked. Confirm that the formula references the checkbox cells, not the task names or a neighboring column. If the boxes use custom values, replace TRUE with the actual checked value.
The total is larger than the task list. Check whether the formula includes another group of checkboxes, old data, or rows beyond the intended list. Also look for a formula placed inside the range it counts.
Unchecked items are not being counted as expected. Verify that the boxes are standard checkboxes storing FALSE. If blank cells or unused rows are included, add a second criterion such as a nonblank task name.
The formula displays an error. Check that the range references are valid and that the formula uses commas between arguments in the sheet’s locale. Some spreadsheet locales use semicolons as separators; use the separator Google Sheets expects for that locale.
The count seems not to refresh. Check that the cells are actual checkbox cells and that the formula points to the right sheet and range. A manually typed word or symbol that looks like a checkbox is not necessarily the Boolean value the formula expects.

When auditing a formula, click a checkbox cell and inspect its value or validation rule. That helps distinguish a standard TRUE/FALSE checkbox from a custom-value checkbox, a plain text entry, or a blank cell. It is often faster than rebuilding the count formula without knowing what the range contains.

How this compares with Excel

The counting idea is similar in Google Sheets and Excel: if the checkbox cell supplies TRUE when selected and FALSE when cleared, a conditional count can count those values. The main difference is how checkboxes are added and configured. In Google Sheets, use Insert > Checkbox; Excel’s checkbox availability and interface depend on the Excel version, and older workbooks may use form controls linked to cells instead.

If a workbook moves between the programs, verify the cell values and formula ranges rather than assuming the visible controls behave identically. A linked Excel form control, for example, may store its result in a separate linked cell. The count should target the cells that actually contain the checked state.

Frequently Asked Questions

Can Google Sheets count checkboxes automatically?
Yes. A formula such as =COUNTIF(B2:B100,TRUE) updates when a standard checkbox in that range is checked or unchecked. The formula needs to reference the cells that contain the checkboxes.

How do I count only checked boxes?
Use COUNTIF with TRUE as the criterion. For example, if checkboxes are in C2:C50, enter =COUNTIF(C2:C50,TRUE) in another cell.

How do I count unchecked checkboxes?
For a range containing only standard checkbox cells, use FALSE as the criterion. If the range includes unused rows, add a condition for a nonblank task name so empty rows are not treated as unfinished work.

Why does COUNTIF return zero for my checkboxes?
The range may point to the wrong cells, or the checkboxes may use custom checked values instead of TRUE. Check the cell’s validation settings, then use the actual stored checked value as the criterion.

Can I count checked boxes for one category?
Yes. Use COUNTIFS with one condition for the checkbox value and another for the category. Make sure both ranges cover the same rows so each checkbox is matched with its own category.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.