Google Sheets COUNTIF: Count Checked Checkbox Cells

To count checked checkboxes in Google Sheets, use =COUNTIF(B2:B20,TRUE), replacing B2:B20 with the cells containing your checkboxes. Standard checkboxes store checked as TRUE and unchecked as FALSE, so COUNTIF can tally either state directly.

How checkboxes work in Google Sheets

A checkbox in Google Sheets is a cell value with a visual control attached to it. With the default settings, checking the box puts the Boolean value TRUE in the cell; clearing it puts FALSE there. These are logical values, not the text strings “TRUE” and “FALSE,” even though the words may appear when you inspect or use the values in formulas.

To add standard checkboxes in a browser, select the cells and choose Insert > Checkbox. You can then click each box, copy and paste boxes, or fill them down as needed. COUNTIF does not require a special checkbox mode: it evaluates the values in the selected range, just as it would evaluate other cell contents.

Checkboxes are useful for tracking tasks, approvals, attendance, inventory checks, and other yes-or-no decisions. A count formula turns a column of individual choices into a summary, and it updates when a user changes a box.

Count checked and unchecked boxes

For default checkboxes, the criterion TRUE means “count cells whose value is the logical value TRUE.” The range in the example below is B2:B20; change it to match your sheet. The formula returns the number of checked boxes in that range.

If there are 19 checkboxes in B2:B20 and 11 are selected, =COUNTIF(B2:B20,TRUE) returns 11. You can put the formula in any cell outside the checkbox range, such as B21, or in a separate summary area.

To count unchecked boxes instead, use FALSE as the criterion. If eight of those same 19 boxes are clear, =COUNTIF(B2:B20,FALSE) returns 8. For the default setup, the checked and unchecked totals add up to the number of checkbox cells, provided every cell in the range contains a checkbox rather than a blank or some other value.

There is no need to put TRUE in quotation marks for the default checkbox value. Quoting it changes the criterion to text, which is different from the Boolean value stored by an ordinary checkbox. A formula can still appear to work with text in a sheet if the cells contain text rather than actual default checkboxes, but that is a different data setup.

Choose the right range for your sheet

The range is as important as the criterion. Include only the checkbox cells you want counted. If task names are in column A and the checkboxes are in column B, count column B, not the task-name column. Including a header or unrelated values can make a formula harder to understand, even if those cells do not match TRUE.

For a list that will grow, you can count a whole column. With checkboxes in column B, =COUNTIF(B:B,TRUE) counts checked boxes throughout that column, including new rows added later. A whole-column range is convenient, but a bounded range such as B2:B200 may be easier to audit in a very large sheet or when you need to keep a formula focused on a defined table.

Be consistent when copying formulas across a summary table. For example, a range written as B2:B20 will shift if you copy the formula to another location. If you want the range to stay fixed when copying, use dollar signs: =COUNTIF($B$2:$B$20,TRUE). In Google Sheets, those references behave as absolute references when a formula is copied.

Build useful checkbox summaries

A simple checked-box count can answer more than “How many are done?” It can feed a completion percentage or a count by category. For a meaningful percentage, divide completed tasks by the number of tasks, rather than by the number of nonblank cells in the checkbox column if that column might contain blanks.

Assume task names are in A2:A13 and the corresponding checkboxes are in B2:B13. If all 12 rows are tasks and eight boxes are checked, =COUNTIF(B2:B13,TRUE)/COUNTA(A2:A13) returns 0.6667, or about 66.7% when the result cell is formatted as a percentage. In Google Sheets, select the result cell and use Format > Number > Percent to display it as a percentage. If some task-name cells are intentionally blank, COUNTA can be a useful denominator because it counts nonempty task names.

You can also count checked items only when they meet another condition. Suppose A2:A20 contains a category such as Urgent or Routine, and B2:B20 contains default checkboxes. If four urgent items are checked, =COUNTIFS(A2:A20,"Urgent",B2:B20,TRUE) returns 4. COUNTIFS applies both conditions to corresponding rows; keep the criteria ranges the same size and aligned so a category is matched with its own checkbox.

For a compact dashboard, place labels such as “Completed” and “Remaining” beside the formulas and make the ranges easy to identify. Keeping the summary separate from the input checkboxes also reduces the chance that someone overwrites a formula while updating the list.

Custom checkbox values

Google Sheets also lets a checkbox use custom checked and unchecked values. This can help when another formula or workflow expects words such as Done and Pending, or values such as 1 and 0. In the Google Sheets browser interface, select the checkbox cells and open Data > Data validation; add or edit a checkbox rule and enable the option for custom cell values. The exact controls appear in the data-validation panel.

Once custom values are set, COUNTIF must search for the actual checked value rather than TRUE. For instance, suppose C2:C20 contains checkboxes configured to store Done when checked and Pending when unchecked. If 11 are checked, =COUNTIF(C2:C20,"Done") returns 11. Use the same spelling and value configured in the rule; a TRUE criterion is not the right match for a cell that stores the text Done.

Custom values change the underlying cell data, so consider how they affect other formulas, charts, and connected processes before changing an established sheet. If collaborators expect checkboxes to contain TRUE and FALSE, switching to text values can make formulas built around those logical values stop matching.

Common reasons a count looks wrong

The range includes the wrong cells. Check that the formula points to the checkbox column and covers the rows you intend to summarize. A missing final row is an easy source of an off-by-one total.
The boxes use custom values. A checkbox set to store Done or 1 will not be counted by a TRUE criterion. Match the criterion to the configured checked value.
Some cells are blank or not checkboxes. COUNTIF counts only cells matching the criterion. A blank cell is not the same as an unchecked default checkbox, whose value is FALSE.
The cells contain text rather than logical values. Typing the word TRUE into a cell does not necessarily make it the Boolean value TRUE. Verify that the cells have checkbox rules and use the expected values.
The displayed total includes filtered-out rows. COUNTIF evaluates cells in its range whether or not their rows are currently hidden by a filter. A filter changes what is displayed, not the basic COUNTIF range.
The formula uses a mismatched criterion. For standard checkboxes, use TRUE without quotation marks. For custom text values, put the text in quotation marks, as in =COUNTIF(C2:C20,"Done").

When debugging, test a small, known range first. Count how many boxes are visibly selected, confirm the checkbox rule, and compare that manual total with the formula. Also check whether a formula was copied with a shifted range or whether new rows fall outside a fixed range.

COUNTIF compared with other counting options

COUNTIF is the direct choice for one condition: count the cells in a range that match a criterion. COUNTIFS is useful when the row must meet two or more conditions, such as being in a particular category and having its checkbox selected. COUNTA answers a different question: it counts nonempty cells, so it is suitable for counting task names but not for distinguishing checked from unchecked boxes.

Do not use COUNTIF with a criterion of 1 just because a checked box looks like a tick. The visible mark is only the interface; the underlying value determines what the formula should match. For default checkboxes that value is TRUE. For customized checkboxes it is whatever checked value the rule specifies.

In Microsoft Excel, COUNTIF is also available, but checkbox behavior depends on how the boxes were created and on the Excel version. Google Sheets instructions should not be assumed to describe Excel’s form controls or newer in-cell checkboxes. If a workbook is moved between products, verify the stored values and test the summary formula in the destination app.

Frequently Asked Questions

What formula counts checked boxes in Google Sheets?
For standard checkboxes in B2:B20, use =COUNTIF(B2:B20,TRUE). Replace the range with the cells containing your checkboxes.

How do I count unchecked boxes?
Use =COUNTIF(B2:B20,FALSE) for standard checkboxes. This counts unchecked boxes but does not count blank cells.

Why does COUNTIF return zero for my checked boxes?
The range may be wrong, or the checkboxes may store custom values such as Done instead of TRUE. Check the data-validation rule and use its actual checked value as the criterion.

Can COUNTIF count checkboxes in a whole column?
Yes. For default checkboxes in column B, =COUNTIF(B:B,TRUE) counts checked boxes throughout that column. Use a more limited range if you want to exclude other content in the column.

Does COUNTIF ignore rows hidden by a filter?
No. COUNTIF includes matching cells in hidden or filtered-out rows within its range. Filtering changes which rows are visible, not which cells COUNTIF evaluates.

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.