Google Sheets 統計勾選核取方塊：按行、列與條件

Tested in: Live formula checks in Microsoft Excel 16.114.4 for Mac (English UI; system region zh-Hans_US), November 8, 2026; Google Sheets (spreadsheet locale zh_CN), November 9, 2026. Results and platform differences are noted below; this is not a certification of every menu workflow.

Which versions this applies to

Google Sheets on desktop and mobile. The same COUNTIF/COUNTIFS formulas work with Excel’s in-cell checkboxes (Microsoft 365). See the Excel section for the differences.

Example data

Sheet 1: task list (A1:D9)

Column D is checkboxes (Insert > Checkbox): ☑ = checked (TRUE), ☐ = unchecked (FALSE). Column C holds real dates.

Sheet 2: weekly habit grid (A1:H5)

Step 1: Count all checked (and unchecked) boxes

Checked tasks (returns 4 with the sample data):

=COUNTIF(D2:D9, TRUE)

Unchecked tasks (returns 4):

=COUNTIF(D2:D9, FALSE)

To keep counting as you add rows, use an open-ended range. Empty cells below your data aren’t counted because they’re neither TRUE nor FALSE:

=COUNTIF(D2:D, TRUE)

Type TRUE and FALSE without quotes. They’re logical values, and writing them plainly keeps the formula unambiguous.

Step 2: Count checked boxes by row

In the habit grid, put this in I2 and drag down to I5 to get the number of days each habit was done:

=COUNTIF(B2:H2, TRUE)

Sample results: Walk 5, Read 4, Water 7, Stretch 2.

To fill the whole column with one formula (nothing to drag, and new habit rows are easy to include), use BYROW in I2:

=BYROW(B2:H5, LAMBDA(r, COUNTIF(r, TRUE)))

Google’s BYROW documentation says the LAMBDA must take exactly one name (here r, the current row) and return a single value per row, which COUNTIF does.

An alternative that doesn’t use LAMBDA:

=ARRAYFORMULA(MMULT(--(B2:H5=TRUE), SEQUENCE(COLUMNS(B2:H5), 1, 1, 0)))

--(B2:H5=TRUE) turns the grid into 1s and 0s, and multiplying by a column of seven 1s adds up each row.

Make sure the cells below I2 are empty, or the spilled result will be blocked. See Fix “Array Result Was Not Expanded Because It Would Overwrite Data” in Google Sheets.

Step 3: Count checked boxes by column

To count how many habits were done each day, put this in B6 and drag right to H6:

=COUNTIF(B2:B5, TRUE)

Or as one formula in B6:

=BYCOL(B2:H5, LAMBDA(c, COUNTIF(c, TRUE)))

Sample results: Mon 3, Tue 2, Wed 2, Thu 3, Fri 2, Sat 2, Sun 4.

To count every checked box in the grid:

=COUNTIF(B2:H5, TRUE)

That returns 18 with the sample data.

To count habits completed every day (rows where all seven boxes are checked):

=SUMPRODUCT(--(BYROW(B2:H5, LAMBDA(r, COUNTIF(r, TRUE)))=COLUMNS(B2:H5)))

That returns 1 (Water).

Step 4: Count checked boxes by condition

COUNTIFS takes pairs of range and criterion. All ranges must be the same size.

Checked tasks for one owner (Maya: 2):

=COUNTIFS(D2:D9, TRUE, B2:B9, "Maya")

Owner from a cell (put a name, or a drop-down of names, in G1):

=COUNTIFS(D2:D9, TRUE, B2:B9, G1)

Overdue tasks: not checked and due before today:

=COUNTIFS(D2:D9, FALSE, C2:C9, "<"&TODAY())

Checked tasks due within a date range (start in G2, end in G3):

=COUNTIFS(D2:D9, TRUE, C2:C9, ">="&G2, C2:C9, "<="&G3)

A summary table per owner, in one formula. QUERY reads checkbox columns as booleans, so you can filter with true:

=QUERY(A1:D9, "select B, count(A) where D = true group by B label count(A) 'Done'", 1)

Owners with no checked tasks won’t appear in this result. If you get a parse error, see Google Sheets QUERY “Unable to Parse Query String” Error: Causes and Fixes.

Step 5: Show progress as a percentage and a bar

Percent complete (format the cell as a percentage with Format > Number > Percent):

=IFERROR(COUNTIF(D2:D, TRUE) / (COUNTIF(D2:D, TRUE) + COUNTIF(D2:D, FALSE)), 0)

The denominator counts every checkbox, checked or not. IFERROR returns 0% while the list is empty. Sample result: 50%.

In-cell progress bar with SPARKLINE (assuming the percentage is in F2):

=SPARKLINE(F2, {"charttype","bar"; "max",1; "color1","green"})

Google’s SPARKLINE documentation lists "charttype","bar", "max" and "color1" as bar chart options. Setting "max" to 1 makes a full bar equal 100%.

Text progress bar (works anywhere, including exports):

=REPT("■", ROUND(F2*10, 0)) & REPT("□", 10 - ROUND(F2*10, 0))

At 50% this shows ■■■■■□□□□□.

Want this built for you? The Free Google Sheets Habit Tracker Template (Checkboxes + Streaks) uses these formulas plus streak counting.

Custom checkbox values

Google’s help page explains that you can give checkboxes custom values under Data > Data validation > Checkbox > Use custom cell values, for example “Yes” for checked and “No” for unchecked. In that case TRUE no longer matches. Count the custom value:

=COUNTIF(D2:D9, "Yes")

=COUNTIFS(D2:D9, "Yes", B2:B9, "Maya")

Check which values your checkboxes use: click a checked box and look at the formula bar.

Excel vs. Google Sheets

Microsoft’s documentation confirms that a checked in-cell checkbox has the value TRUE and an unchecked one FALSE. In older Excel versions, checkboxes are form controls floating over the grid; they only have a value if each one is linked to a cell (Format Control > Cell link), and you count the linked cells, not the checkboxes themselves.

Common mistakes

1. Using COUNTA to count checked boxes. Unchecked boxes contain FALSE, which is a value, so COUNTA counts every checkbox. Use COUNTIF(..., TRUE).
2. Comparing a checkbox to the text “TRUE”. In formulas like =IF(D2="TRUE", ...), the boolean TRUE is not equal to the text "TRUE", so the test fails. Use =IF(D2=TRUE, ...) or simply =IF(D2, ...).
3. Changing to custom values and forgetting the formulas. After switching to “Yes”/“No”, COUNTIF(..., TRUE) returns 0.
4. Mismatched COUNTIFS ranges. D2:D9 with B2:B20 returns an error. Keep every range the same size.
5. Counting “not done” with FALSE when some rows have no checkbox. Rows without a checkbox are empty, not FALSE. Use =COUNTA(A2:A9) - COUNTIF(D2:D9, TRUE) to count unfinished tasks instead.

FAQ

What value does a checked checkbox have in Google Sheets?

TRUE by default, and an unchecked box is FALSE. If you turned on custom cell values in data validation, the checkbox stores those values instead.

How do I count unchecked checkboxes?

Use =COUNTIF(range, FALSE). Empty cells without a checkbox aren’t counted, because they’re neither TRUE nor FALSE.

How do I count checkboxes based on another column?

Use COUNTIFS with the checkbox range and TRUE, then the other column and its criterion, for example =COUNTIFS(D2:D9, TRUE, B2:B9, “Maya”).

How do I show the percentage of checked boxes?

Divide checked by total checkboxes: =COUNTIF(D2:D, TRUE)/(COUNTIF(D2:D, TRUE)+COUNTIF(D2:D, FALSE)), then format as a percent. Wrap it in IFERROR to show 0% when the list is empty.

Why does COUNTIF return 0 even though boxes are checked?

Most often the checkboxes use custom values like “Yes”, so TRUE never matches. Click a checked box, read its value in the formula bar, and count that exact value.

Related tutorials

• All templates (cluster hub)
• COUNTIF vs COUNTIFS vs SUMPRODUCT: When to Use Each (With Examples)

Live-test findings

Native Sheets checks covered default TRUE/FALSE checkboxes, custom checkbox values, COUNTIF/COUNTIFS, BYROW and BYCOL counts, and QUERY summaries. Count the stored checkbox value, not the visual presence of a checkbox. SPARKLINE formulas evaluated without error; their graphical appearance was not inspected.

Sources

• Google Docs Editors Help: Add & use checkboxes (Insert > Checkbox, custom cell values): https://support.google.com/docs/answer/7684717
• Google Docs Editors Help: COUNTIF: https://support.google.com/docs/answer/3093480
• Google Docs Editors Help: BYROW function (one-name LAMBDA, single value per row): https://support.google.com/docs/answer/12570930
• Google Docs Editors Help: SPARKLINE (bar chart options max, color1): https://support.google.com/docs/answer/3093289
• Google for Developers: Query Language Reference (boolean literals true/false, group by, label): https://developers.google.com/chart/interactive/docs/querylanguage
• Microsoft Support: Using check boxes in Excel (checked = TRUE, unchecked = FALSE): https://support.microsoft.com/en-us/excel/using-check-boxes-in-excel