Count Checked Boxes in Google Sheets: Tasks, Categories and Completion Rate
A checklist may have checkboxes in hundreds of prepared rows but only a handful of real tasks. Counting every unchecked box makes the progress figure look much worse than the actual work. Custom checkbox values can also make a familiar TRUE formula return the wrong count.
Create actual checkbox cells rather than trusting CSV text
Use Google Sheets in a desktop browser. Put task_id and category from the fixture into A1:B7 and done into C1. Select C2:C7 and use Insert > Checkbox. Check C2, C4 and C6; leave C3, C5 and C7 unchecked. C7 belongs to a template row with no task ID.
A CSV stores values, not checkbox validation rules. Importing the supplied TRUE and FALSE strings is therefore only a data reference, not a guarantee that your sheet contains interactive checkboxes. Inspect the formula bar or validation rule if the imported column behaves differently. The manual setup above makes the fixture's logical states explicit.
Compare the simple count with the task count
Put the first formula below in E2 to count all checked states. It returns three. The second formula returns three unchecked values because it also includes C7. Both results accurately describe the selected checkbox cells, but only one unchecked count describes the real work.
For a progress report, make task_id the record boundary. A checkbox without a task ID is a prepared control, not a pending task. This distinction becomes important when a template contains hundreds of unchecked boxes. The rule should be written into the formula instead of depending on someone to stop selecting the range at exactly the right row.
All checked values: =COUNTIF(C2:C7,TRUE)
All unchecked values: =COUNTIF(C2:C7,FALSE)Count completed and unchecked real tasks
Use the formulas below in E4:E6. They require a nonblank A cell, so the template row is excluded. Expected values are three completed tasks, two unchecked tasks and five total tasks. Keep each criteria range aligned to rows 2 through 7.
This example assumes every real task has exactly one default checkbox containing TRUE or FALSE. If a task's checkbox was deleted, the completed-plus-unchecked result may be smaller than the task total. Treat that mismatch as a data check. Do not quietly redefine every missing value as an unchecked task if a missing control signals an incomplete import.
E4: =COUNTIFS(A2:A7,"<>",C2:C7,TRUE)
E5: =COUNTIFS(A2:A7,"<>",C2:C7,FALSE)
E6: =COUNTIF(A2:A7,"<>")
Calculate a rate with an explicit denominator
In E7 enter the formula below and format the result as a percentage. It divides the completed-task count by the number of task IDs, producing 60%. If there are no task IDs, it returns an empty display rather than reporting a meaningless zero divided by zero.
An empty rate and a 0% rate have different meanings: no tasks have been defined in the first case, while defined tasks remain incomplete in the second. Explain that distinction wherever the percentage is displayed. For this fixture, completed plus unchecked equals total; keep that simple reconciliation near the summary while you are building it.
E7: =IF(E6=0,"",E4/E6)
Consistency check: =E4+E5=E6Add category totals without counting the template
Use G1:J3 for category, tasks, completed and completion. Put Setup in G2 and Review in G3. The formulas below go in H2:J2 and are filled down one row. Setup must be one completed task out of two; Review must be two out of three.
Keep task IDs nonblank and category spelling consistent. The category text in G2 is a criterion, not a formula fragment. A renamed category may create a zero-row report until the summary label changes. The download preserves the higher-precision ratios; the visible percentage may round Review to 66.67% depending on your chosen display format.
H2: =COUNTIFS($A$2:$A$7,"<>",$B$2:$B$7,G2)
I2: =COUNTIFS($A$2:$A$7,"<>",$B$2:$B$7,G2,$C$2:$C$7,TRUE)
J2: =IF(H2=0,"",I2/H2)Use the configured values for custom checkboxes
Create the custom fixture on a separate tab. In Data > Data validation, inspect the Checkbox rule and its custom values. Set checked to Done and unchecked to Todo, then reproduce the same pattern of checked states. The COUNTIFS criteria must now use those text values rather than TRUE and FALSE.
The visual ticks are identical, but the cell values have changed. A weighted checkbox that stores a number is another model again: counting completed tasks and summing earned points are separate calculations. Keep one explicit convention per reporting range, or split the ranges by convention before combining totals.
Completed custom tasks: =COUNTIFS(A2:A7,"<>",C2:C7,"Done")
Unchecked custom tasks: =COUNTIFS(A2:A7,"<>",C2:C7,"Todo")
Keep the checklist auditable as rows are added
Extend the task, category and checkbox ranges together. Before distributing the sheet, add one new real task and one empty template row on a copy. The real task should change the denominator; the template should not. Check that the consistency formula still returns TRUE and the category totals reconcile to the overall total.
The downloadable files validate states and arithmetic, not interactive checkbox behavior. Their independent checks do not create data validation or click controls in Google Sheets. A final native check should inspect default and custom rules, toggle one box, and confirm the summary updates. This article covers counting and progress; coloring a row from a checkbox is a separate presentation task.
Common questions
Why does my unchecked count include hundreds of rows?
Prepared checkboxes can contain FALSE even where no task exists. Add a nonblank task ID criterion with COUNTIFS.
Why does TRUE stop counting after I customize the boxes?
The checked value may now be text or a number. Inspect the validation rule and use that exact configured value.
Can I use COUNTA on the checkbox column for total tasks?
It measures populated cells rather than real task records. Use the task-ID column for the denominator and validate the checkbox states separately.
Sources & method
- Google Docs Editors Help: Add and use checkboxes ↗
- Google Docs Editors Help: COUNTIF ↗
- Google Docs Editors Help: COUNTIFS ↗
- Google Docs Editors Help: IF ↗
Examples and diagrams use synthetic data. Application instructions follow the linked documentation; available menus and options can vary. Our sample checks do not establish behavior in every Excel or Google Sheets version. Read our AI-assisted editorial method.