Google Sheets: Count Checked and Unchecked Checkboxes

To count checked checkboxes in Google Sheets, use =COUNTIF(A2:A20,TRUE), assuming the checkboxes are in A2:A20 and use the default values. Count unchecked boxes in that range with =COUNTIF(A2:A20,FALSE). These formulas count the stored checkbox values, not the visible check-mark symbols.

How checkbox values work in Google Sheets

A checkbox in Google Sheets is a cell with a data-validation rule. With the standard setup, checking it stores the Boolean value TRUE, and leaving it unchecked stores FALSE. The check mark is simply how Sheets displays that value in the cell. This is why COUNTIF can count boxes by looking for TRUE or FALSE.

That distinction matters when you build formulas or share a sheet with other people. A cell that looks checked is not normally storing the text “TRUE”; it contains a logical value. In a formula, write TRUE without quotation marks to match the standard checked value. Writing "TRUE" asks for text instead, which may not match the checkbox value.

Checkboxes also do not automatically make an entire column a checklist. Only cells where a checkbox has been added behave as checkbox cells. If your formula range includes ordinary blank cells, COUNTIF ignores them when looking for TRUE or FALSE. This can be useful when a list grows over time, but it is still good practice to choose a range that matches the part of the sheet where checkboxes belong.

Count checked checkboxes

Use COUNTIF when you need the number of completed, selected, or approved items. The function takes a range and a condition. In this case, the condition is TRUE. For example, if a task list has checkbox cells in C2:C50, use:

With checkboxes in C2:C50, enter =COUNTIF(C2:C50,TRUE). If 18 boxes are checked, the result is 18.

Put the formula in a cell outside the checkbox range so it does not overwrite a checkbox or become part of the range it counts. You could place a label such as “Completed” in B1 and the formula in C1, for example. When someone checks or unchecks a box, the count updates automatically; you do not need to recalculate it manually.

A practical checklist might have task names in column B and completion boxes in column C. The count formula should refer to column C only. Including task names in the range is unnecessary and can make the formula harder to understand, even if text entries do not match TRUE.

Count unchecked boxes and total responses

To count unchecked boxes, use FALSE as the criterion. With checkbox cells in C2:C50, enter =COUNTIF(C2:C50,FALSE). If 18 of 49 checklist boxes are checked and all 49 cells contain checkboxes, this formula returns 31 unchecked boxes.

That result is only a count of FALSE values, not necessarily the number of items remaining in a list. If the range contains empty cells where no checkbox has been added, those cells are not counted as unchecked. This helps distinguish an intentionally unchecked box from a row that has not been set up yet.

If you want to count all boxes that currently have one of the default checkbox values, add the checked and unchecked counts. With checkboxes in C2:C50, use =COUNTIF(C2:C50,TRUE)+COUNTIF(C2:C50,FALSE). This counts cells containing either Boolean value and ignores blanks. For a range made entirely of standard checkboxes, the result is the total number of checkbox responses, whether selected or not.

Be clear about what the total means when reporting it. A count of checked boxes can represent completed tasks, while a count of TRUE and FALSE cells represents boxes that have been set up. Neither formula tells you how many people responded unless each row or box corresponds to one person and the sheet’s data is organized that way.

Use custom checkbox values

Google Sheets lets you assign custom values to checked and unchecked boxes. This can be helpful when a sheet needs words such as “Approved” and “Pending” instead of TRUE and FALSE. To configure one, select the relevant cells and use Data > Data validation. In the criteria settings, choose Checkbox and enable the option to use custom cell values, then enter the values for checked and unchecked states.

When a checkbox has custom values, count those values rather than TRUE and FALSE. For example, suppose the checkboxes in D2:D30 are set to store “Approved” when checked and “Pending” when unchecked. With those cells as the range, enter =COUNTIF(D2:D30,"Approved") to count approved entries. To count pending entries, use =COUNTIF(D2:D30,"Pending").

Custom values can make a sheet easier to read in formulas or when data is exported, but they also change what other formulas should expect. A formula that searches for TRUE will not count a cell that stores “Approved.” If a count unexpectedly returns zero, check the checkbox settings and the underlying values before changing the formula. Also use the same spelling and spacing as the custom value; “Pending” and “Pending ” are not identical text.

Count checked boxes that meet another condition

Sometimes the useful question is not just how many boxes are checked, but how many are checked for a particular category, person, or status. COUNTIFS applies multiple conditions to corresponding ranges. For example, assume A2:A20 contains checkboxes and B2:B20 contains region names. To count checked boxes for rows marked North, use:

With checkboxes in A2:A20 and region names in B2:B20, enter =COUNTIFS(A2:A20,TRUE,B2:B20,"North"). It counts only rows where the box is checked and the region is North.

Each condition range in COUNTIFS should cover the same rows and have the same dimensions. If one range starts in row 2 and another starts in row 3, the formula may compare the wrong records or return an error. Keep the checkbox and category ranges aligned, and include the header row in neither range.

This approach works for many common summaries: checked tasks assigned to one person, approved requests in one department, or completed items with a particular priority. If the checkboxes use custom values, replace TRUE with the checked value, such as “Approved.” The other condition can remain a text value, number, or other appropriate criterion.

Add checkboxes to a range

If you have not added the checkboxes yet, select the cells where they should appear and choose Insert > Checkbox in Google Sheets in a web browser. Then place the COUNTIF formula in a separate summary cell. You can add checkboxes to a selected block of cells at once, which is often quicker and more consistent than inserting them individually.

For a growing list, decide whether the formula should include future rows. A fixed range such as C2:C50 is easy to read and limits the count to a known set of items. A range such as C2:C includes later rows in column C, which can be convenient for an expanding checklist. An open-ended range may also include unrelated checkboxes lower down the column, so keep the column dedicated to the same kind of data.

Keep the header outside the checkbox range and formula range.
Use one checkbox column for one kind of response when possible.
Put summary formulas in cells that will not be overwritten when the list grows.
For custom values, document what the checked and unchecked states mean.

Common counting problems

If the formula returns zero even though boxes look checked, first confirm that the formula refers to the correct cells. A checklist may have moved to another column, or the visible checkboxes may be outside the range you counted. Next, check whether the boxes use custom values. If checked boxes store “Yes,” a condition of TRUE will not match them; count “Yes” instead.

Another frequent issue is mixing up the range with the criterion. In =COUNTIF(C2:C50,TRUE), C2:C50 is the range being examined and TRUE is the value being counted. A formula that counts the task-name column instead of the checkbox column will not answer the intended question. Make sure the formula refers to the cells that actually contain the checkboxes.

Blank cells are not the same as unchecked boxes. If someone clears a checkbox cell or a row has not yet received a checkbox, it may be blank rather than FALSE. COUNTIF with FALSE does not count that blank. If your goal is to audit setup, compare the number of boxes that have a TRUE or FALSE value with the number of expected rows, rather than assuming every blank represents an unchecked item.

Finally, check for extra spaces or inconsistent custom text values, and verify that your formula has balanced parentheses and quotation marks. Google Sheets formulas generally use commas between arguments in many locales, but spreadsheet locale settings can affect formula separators. If Sheets flags a formula as a parse error, check the separator expected by that file’s locale.

COUNTIF versus other counting methods

COUNTIF is usually the clearest tool for counting checked or unchecked boxes because it states the condition directly. COUNTA counts nonempty cells, so it may be useful for counting filled checkbox cells, but it does not distinguish checked from unchecked. If a range contains labels, notes, or other values, COUNTA can include those too.

COUNT is designed to count numeric values, not to distinguish the Boolean TRUE and FALSE states of standard checkboxes. SUM can be useful in some spreadsheet designs where values are deliberately stored as numbers, but standard Google Sheets checkboxes use logical values by default. For a normal checklist, COUNTIF with TRUE or FALSE is easier to interpret and less likely to confuse a later editor.

For a single condition, use COUNTIF. For two or more conditions, use COUNTIFS. Choose the function based on the question: “How many boxes are checked?” calls for COUNTIF; “How many are checked and assigned to North?” calls for COUNTIFS. Both update as the underlying cells change.

Frequently Asked Questions

What formula counts checked checkboxes in Google Sheets?
Use =COUNTIF(A2:A20,TRUE), replacing A2:A20 with the range containing your standard checkboxes.

How do I count unchecked checkboxes?
Use =COUNTIF(A2:A20,FALSE). It counts unchecked boxes that store the default Boolean FALSE value, but does not count blank cells.

Can I count checkboxes that use custom values?
Yes. Use COUNTIF with the checked or unchecked custom value. For example, if checked boxes store “Yes,” count them with =COUNTIF(A2:A20,"Yes").

Why does COUNTIF return zero for checked boxes?
Check that the formula range contains the boxes and that the boxes use the default TRUE value. If they have custom values, use the exact checked value as the criterion instead.

Does COUNTIF include blank cells as unchecked?
No. A blank cell is not the same as a checkbox containing FALSE, so COUNTIF with FALSE ignores empty cells. Add checkboxes to the intended range or account for blanks separately when auditing a list.

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.