Spreadsheet Practice

How to count checked checkboxes in Excel and Google Sheets

Formula (Excel and Google Sheets)

=COUNTIF(B2:B9,TRUE)

A checked box holds TRUE, so count the TRUEs: =COUNTIF(B2:B9,TRUE).

Result on the example
5 in E1
Works in
Every current version
Google Sheets
Same formula

See it work

The formula is already in E1. Change any input and the result updates, or edit the formula to experiment.

How it works

Checkboxes in Google Sheets and in current Excel 365 are cells that hold TRUE when checked and FALSE when not. COUNTIF with TRUE, without quotes, counts the checked ones.

Five of the eight tasks are done.

=COUNTIF(B2:B9,TRUE)

Result5

  1. B2:B98 rows × 1 col: TRUE, FALSE, TRUE, TRUE, …

    A range: 8 cells in one column.

  2. COUNTIF(B2:B9,TRUE)5

    COUNTIF: Counts cells that meet one condition.

    • rangeB2:B98 rows × 1 col: TRUE, FALSE, TRUE, TRUE, …
    • criteriaTRUE

In Google Sheets

The same formula works in Google Sheets: =COUNTIF(B2:B9,TRUE). We ran it in Google Sheets mode on the same data and it returns 5.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=SUMPRODUCT(--B2:B9)5TRUE becomes 1 and FALSE 0, so the sum is the number checked.
=COUNTIF(B2:B9,FALSE)3Different resultCounts the unchecked boxes. Counts the 3 unchecked tasks.

Common mistakes

  • Older Excel checkboxes (Form Controls) float over the sheet and hold no value. Link each one to a cell (right-click, Format Control, Cell link) and count the linked cells.
  • In Google Sheets a checkbox can use custom values, like "Done" for checked. Then count that value: =COUNTIF(B2:B9,"Done").

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Count checked checkboxes

In E1, count the tasks whose Done box in B2:B9 is checked (TRUE).

Questions

How do I show the percentage checked?

Divide by the number of boxes: =COUNTIF(B2:B9,TRUE)/ROWS(B2:B9), formatted as a percentage.

How do I count checked boxes for one person?

Use COUNTIFS: =COUNTIFS(B2:B9,TRUE,C2:C9,"Ana").

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.