Google Sheets: Count Checked Checkboxes with COUNTIF

To count checked checkboxes in Google Sheets, use =COUNTIF(B2:B20,TRUE), replacing B2:B20 with the cells that contain your checkboxes. A checked standard checkbox is stored as the Boolean value TRUE, so COUNTIF can count it directly.

How checkboxes work in Google Sheets

A checkbox in Sheets is a cell with a selectable checked or unchecked state. By default, checking it puts the Boolean value TRUE in the cell; clearing it puts FALSE there. Those are logical values, not text strings that happen to spell “TRUE” and “FALSE.” This distinction is why a formula that counts TRUE values is usually the simplest solution.

Checkboxes can be used for task lists, attendance, approvals, inventory checks, and other yes-or-no questions. They do not create a separate visual object outside the grid: each checkbox belongs to a cell, and formulas refer to that cell just as they would refer to a number or text value. If you copy a checkbox cell, you also copy its validation rule and state, so confirm that the copied range is the one your summary formula is meant to count.

In a web browser, add standard checkboxes by selecting the target cells and choosing Insert > Checkbox. You can also select a range first to add checkboxes to several cells at once. The same cells can then be counted in a formula elsewhere in the sheet.

Count checked boxes with COUNTIF

Suppose a volunteer sign-up sheet has checkboxes in B2:B8. If B2, B4, B5, and B8 are checked, the range contains four TRUE values. With those checkboxes in B2:B8, enter this formula in a separate summary cell:

=COUNTIF(B2:B8,TRUE)

The result is 4. COUNTIF takes a range and a condition: here, it examines B2:B8 and counts cells whose value is TRUE. The formula recalculates when someone checks or clears a box, so you do not need to update the total manually.

Use a range that covers the checkbox cells, not the cell containing the total. For a longer sheet, you might count B2:B100, or use the whole column if the column contains only checkbox values and a header. A bounded range is often easier to understand and avoids including unrelated content farther down the sheet.

Count unchecked boxes or calculate completion

Because unchecked standard boxes contain FALSE, COUNTIF can count those too. If the checkboxes are in B2:B8 and three are unchecked, this formula returns 3:

=COUNTIF(B2:B8,FALSE)

To calculate the share of answered items that are checked, divide the checked count by the number of populated checkbox cells. If all seven cells in B2:B8 contain checkboxes and four are checked, use this formula in a summary cell:

=COUNTIF(B2:B8,TRUE)/COUNTA(B2:B8)

The result is approximately 0.5714, or 57.14% when the result cell is formatted as a percentage. In the Sheets toolbar, choose the percent formatting option to display it as a percentage. This calculation assumes every cell in the range contains a checkbox; if blanks are present, COUNTA excludes them, which is useful when blank rows mean “not applicable yet.” If every row represents a required task, make sure blanks are not accidentally omitted from the denominator.

Count checks by row or by another condition

You can count several task boxes on one row to show how many steps a person has completed. For example, if task checkboxes for one person are in B2:D2, and two are checked, enter this in the row’s summary cell:

=COUNTIF(B2:D2,TRUE)

The result is 2. Copying the formula down changes the relative row reference, so the next row will count B3:D3. This is useful for progress columns, though it counts completed tasks rather than calculating the percentage of tasks completed. Divide by the number of applicable task cells if you need a percentage.

You can also count checked boxes only for rows that meet a second condition. Imagine B2:B20 contains checkboxes and C2:C20 contains a status. If you want the checked boxes only on rows marked Complete, use this formula in a summary cell:

=COUNTIFS(B2:B20,TRUE,C2:C20,"Complete")

It returns the number of rows where the checkbox is checked and the corresponding status cell says Complete. COUNTIFS is useful for dashboards where a checkbox alone is not enough to define the group—for example, counting approved requests that are also assigned to a particular team.

Custom checkbox values change the formula

Google Sheets lets you configure checkbox values rather than using TRUE and FALSE. For example, a checked box could store “Yes” and an unchecked box could store “No.” To set this up, select the cells and open Data > Data validation. Under Criteria, choose Checkbox, then enable the option for custom cell values and enter the checked and unchecked values.

If B2:B8 uses “Yes” for checked and “No” for unchecked, COUNTIF must search for the custom checked value. With those custom-valued checkboxes in B2:B8, use:

=COUNTIF(B2:B8,"Yes")

If four cells contain Yes, the result is 4. A formula that looks for TRUE would not count those cells, because the stored value is now the text Yes. Match the formula’s criterion to the actual checked value configured in the checkbox rule. If you are unsure which values are in use, select a checkbox cell and inspect the formula bar or its data-validation rule.

Common reasons a checkbox count looks wrong

The range is incorrect. Check that the range includes all intended checkbox cells and does not stop before the last row. Also verify that the summary formula is not inside the range being counted.
The checkboxes use custom values. A TRUE criterion will not match a checked value such as Yes or 1. Use the configured checked value as the COUNTIF criterion.
The cells only look like checkboxes. A typed check mark, the word TRUE, or a symbol inserted as text is not necessarily the Boolean value stored by a checkbox. COUNTIF with TRUE counts the logical value, not every character that resembles a check.
Some cells are blank or have different validation. A partially filled range may contain blank cells or a mixture of standard and custom checkboxes. Review a few cells that do not behave as expected and confirm that their rules and values match.
Rows are hidden or filtered out. COUNTIF counts matching values in its range even when rows are hidden by a filter. A displayed subset may therefore appear to disagree with the total. Decide whether the report should represent every row or only visible rows before choosing a summary method.
The formula uses the wrong argument separator for the spreadsheet locale. The examples here use commas. Some locale settings use semicolons in formulas; if Sheets rejects a formula as a parse error, check the spreadsheet’s locale and adjust separators as required.

A quick diagnostic is to click one box that should count and check that its cell contains the expected value. If the formula is intended to count standard checkboxes, a checked cell should evaluate as TRUE. This simple check can distinguish a formula-range problem from a data-validation or value problem.

COUNTIF compared with other counting options

COUNTIF is usually the clearest function when the question is “How many boxes are checked?” COUNTA answers a different question: how many cells are not empty. Since both checked and unchecked standard checkboxes contain values, COUNTA generally counts both states, not just the checked ones. COUNTBLANK likewise counts empty cells; it is not a substitute for counting unchecked boxes when unchecked cells contain FALSE.

For multiple criteria, use COUNTIFS, as in the status example above. For a straightforward checkbox total, COUNTIF is simpler to read and maintain. You could also build a sum of TRUE/FALSE values in some spreadsheet contexts, but that approach is less clear to readers and can behave differently when the range contains text or custom values. COUNTIF makes the intended condition explicit.

Excel also supports COUNTIF and standard checkbox controls, but the interface for inserting checkboxes can differ by Excel version and setup. These steps and menu paths refer to Google Sheets in a web browser. If you open the file in Excel, verify that the checkbox controls and their stored values carry over as expected; do not assume every Sheets validation feature behaves identically in Excel.

Make a reliable checkbox summary

For a shared tracker, place the total near the top of the sheet and label it clearly, such as “Tasks checked.” Keep the checkbox column dedicated to checkbox values, and put names, dates, and notes in separate columns. This makes the count range easier to audit and reduces the chance that someone will paste unrelated data into it.

Before relying on a total, test a small sample: check one box and confirm the count increases by one, then clear it and confirm the count decreases. If a sheet uses custom values or has blank rows, test those cases too. Protecting a summary formula cell can help prevent accidental edits in a collaborative sheet; use Data > Protect sheets and ranges to manage protections, while remembering that permissions and organizational settings affect who can edit protected areas.

For a dashboard that should show only a subset, create a clearly defined rule for that subset—for example, a team, date period, or status—and count with COUNTIFS where appropriate. A single overall COUNTIF is accurate for the range it sees, but it cannot infer which rows a reader considers relevant. Clear range boundaries and labels make the number easier to interpret.

Frequently Asked Questions

Does COUNTIF count a checkbox when it is checked?
Yes. A standard checked checkbox stores TRUE, so COUNTIF with TRUE as its criterion counts it. If the checkbox has custom values, use its configured checked value instead.

How do I count unchecked checkboxes in Google Sheets?
For standard checkboxes, count FALSE values with COUNTIF over the checkbox range. This counts unchecked boxes, not blank cells; use a separate blank count only if blank cells have a meaning in your sheet.

Can I count checked boxes in more than one column?
Yes. If the checkbox cells form one continuous rectangular range, include that whole range in COUNTIF. If the columns are separated by unrelated data, use separate COUNTIF calculations and add their results, or reorganize the checkbox data into a contiguous range.

Why does my formula return zero even though boxes are checked?
Common causes include counting the wrong cells, using TRUE when the checked value is custom text, or having cells that only look like checkboxes. Inspect a checked cell and confirm its stored value and validation rule.

Does COUNTIF ignore filtered-out rows?
No. COUNTIF evaluates the cells in its specified range, including rows hidden by a filter. If your intended total is only for currently visible rows, use a method designed around visible rows rather than assuming COUNTIF follows the filter display.

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.