Count Checkboxes in Google Sheets with 2 Simple Formulas

Google Sheets keeps adding useful features, and the checkbox in Google Sheets is one of the most practical. It makes checklists interactive, and it also makes dashboards easier because you can count completed items instantly.

Unlike Excel, Google Sheets stores a checkbox inside a cell like any other value. By default, a checked box evaluates to TRUE and an unchecked box evaluates to FALSE. That is why counting checkboxes is usually just counting TRUE or FALSE values.

In this tutorial, you will learn (using my years of experience) how to count checkboxes in Google Sheets using COUNTIF, COUNTIFS, and a few dashboard friendly formulas. You will also learn how to scale your tracker with ARRAYFORMULA so you do not need to copy formulas down as your sheet grows.

Count the Number of Checkboxes in Google Sheets

When you insert a checkbox in Google Sheets, it can take two values by default, TRUE (checked) or FALSE (unchecked).

If you want to count checked checkboxes, you count the TRUE values in the checkbox range. If you want to count unchecked checkboxes, you count the FALSE values.

Count checkboxes that are checked

Suppose you have a dataset like the one below, and you want to count how many tasks are completed (checked) across a range of cells. The easiest option is COUNTIF.

Use this formula to count checked boxes:

=COUNTIF(B2:B11,TRUE)

If you want to count unchecked boxes (often used as “pending tasks”), use:

=COUNTIF(B2:B11,FALSE)

=COUNTIF(B2:B11,TRUE)/(COUNTIF(B2:B11,TRUE)+COUNTIF(B2:B11,FALSE))

Count checkboxes based on a condition (COUNTIFS)

You can also count checkboxes using multiple conditions with COUNTIFS. This is useful for phase based project trackers, categories, owners, and month based reporting.

Suppose you have tasks grouped by phase, and you want to count how many tasks in Phase 1 are completed.

Use this formula:

=COUNTIFS(C2:C11,TRUE,A2:A11,E2)

In this example, COUNTIFS checks two conditions:

• The checkbox is checked (TRUE).
• The phase matches the selected phase (Phase 1 in the cell E2).

Only rows that meet both conditions are counted.

Using ARRAYFORMULA with checkboxes (auto-fill helper columns and summaries)

COUNTIF and COUNTIFS already work on ranges, but ARRAYFORMULA becomes valuable when you want your sheet to scale without copying formulas down.

The most common pattern is to use ARRAYFORMULA to create a helper column that converts checkbox results into numbers (1 for checked, 0 for unchecked). Once you have that, you can SUM, QUERY, chart, and build dashboards more cleanly.

Create an auto-filled 1/0 helper column from checkboxes

Assume your task names are in column A and checkboxes are in column B. Put this in C2:

=ARRAYFORMULA(IF(LEN(A2:A),IF(B2:B=TRUE,1,0),))

This fills the entire column automatically:

• Rows with a task name get 1 if checked, 0 if unchecked.
• Blank rows stay blank, so your sheet stays clean.

Now you can count completed tasks with a simple SUM:

=SUM(C2:C)

And you can calculate percent complete with:

=IFERROR(SUM(C2:C)/(COUNTIF(B2:B,TRUE)+COUNTIF(B2:B,FALSE)),0)

Auto-build a “completed by phase” summary table (great for charts)

If your phase or category is in column A and your checkbox is in column B, you can generate a live summary table with QUERY. This updates as you add more rows.

Put this in an empty area, for example E2:

=QUERY({A2:A,ARRAYFORMULA(IF(B2:B=TRUE,1,0))},
"select Col1, sum(Col2)
 where Col1 is not null
 group by Col1
 label sum(Col2) 'Completed'",0)

This returns a two-column table you can chart immediately:

• Phase (or category)
• Completed count

ARRAYFORMULA when checkboxes use custom values

If you assigned custom checkbox values like “yes” and “no”, use those values inside the helper formula. Example (checkbox column B, tasks in A):

=ARRAYFORMULA(IF(LEN(A2:A),IF(B2:B="yes",1,0),))

Once you have the 1/0 helper column, SUM, percent complete, charts, and summaries work the same way.

How to add custom values to checkboxes

Google Sheets also lets you assign custom values to your checkboxes instead of TRUE and FALSE. This is useful when you want a checkbox to store “yes/no”, “done/not started”, or even numeric scoring values.

1. Go to Data > Data validation.

2. In the data validation window, open the criteria drop down menu and select Checkbox.

3. Select Use custom cell values.
4. Enter the checked and unchecked values you want. For example, checked = yes, unchecked = no.
5. Click Save.

After saving, checking the box will show the custom value in the formula bar.

Count checkboxes when using custom values

If your checkboxes use custom values, you count the checked values by matching the custom “checked” entry (and the unchecked values by matching the custom “unchecked” entry).

For example, if checked = yes and unchecked = no, use:

=COUNTIF(B2:B11,"yes")

To count unchecked boxes in that setup:

=COUNTIF(B2:B11,"no")

=SUM(B2:B11)

Dashboard-friendly formatting hacks using checkboxes

Grey out completed rows automatically (conditional formatting)

This is one of the quickest ways to make a checklist easier to scan. When a checkbox is checked, the entire row fades, so the remaining work stands out.

1. Select the rows you want to format, for example A2:D11.
2. Go to Format > Conditional formatting.
3. Choose Custom formula is.
4. Use a formula like: =$B2=TRUE (replace B with your checkbox column).
5. Pick a light gray text color or a light gray fill, then save.

Add a progress bar to your dashboard

If you want a quick visual, you can turn percent complete into a bar using SPARKLINE. This works well in a single dashboard cell.

Checkbox only percent complete:

=COUNTIF(B2:B11,TRUE)/(COUNTIF(B2:B11,TRUE)+COUNTIF(B2:B11,FALSE))

Progress bar based on that percent:

=SPARKLINE(COUNTIF(B2:B11,TRUE)/(COUNTIF(B2:B11,TRUE)+COUNTIF(B2:B11,FALSE)),{"charttype","bar";"max",1})

How to make a chart with checkbox data

After counting checkboxes, you can chart the results to visualize completion, especially for dashboards and status reports.

1. Select the cell range of data you want to use.

2. Go to Insert > Chart.

At first, the chart may show no data until the chart editor settings match your range.

3. In the chart editor, enable Use row 1 as headers and keep Switch rows and columns unchecked.

4. Ensure the x-axis is treated as a label if your chart type needs it.

5. Choose a chart type that fits the story, for example a pie chart for completed vs remaining, or a bar chart for phase comparisons.

FAQ

Conclusion

That is how to count checkboxes in Google Sheets, including checked, unchecked, conditional counts with COUNTIF and COUNTIFS, and scalable dashboards using ARRAYFORMULA. Once you understand what the checkbox returns, it becomes easy to build reliable checklist reports.

I hope you found this tutorial useful.

Other Google Sheets tutorials you may also like:

• Count Cells based on the Cell Color in Google Sheets
• Count the Number of Characters in a Cell in Google Sheets
• How to Create a Drop-Down List in Google Sheets?
• How to Insert Check Mark Symbol In Google Sheets
• Google Sheets Counting Cells With Text