Google Sheets Checkbox Formula: If Checked, Then What?

In Google Sheets, a checked checkbox evaluates to TRUE and an unchecked checkbox evaluates to FALSE, so a formula such as =IF(A2,"Done","To do") can return a different result for each state. Replace A2 with the cell containing your checkbox, then change the two result values to suit your sheet.

How Google Sheets checkboxes work

A checkbox is a cell value displayed as a small box. With the standard checkbox settings, checking it stores the Boolean value TRUE, and clearing it stores FALSE. That is why you can use the checkbox cell directly in a logical formula instead of checking whether it contains a particular word.

To add standard checkboxes, select the cells where you want them and choose Insert > Checkbox. For example, if A2:A10 contains checkboxes, each row can hold a separate task status. A formula in another column can respond to the checkbox on the same row.

A checkbox is not just a visual decoration: its underlying value matters to formulas. If you copy and paste a checkbox cell, you may copy its value or validation rule depending on how you paste. When a formula behaves unexpectedly, inspect the cell’s checkbox settings and value rather than relying on how the box looks.

Use IF to return a result

The basic pattern is IF(condition, value_if_true, value_if_false). The condition is the checkbox cell. Sheets returns the second argument when the checkbox is checked and the third when it is unchecked.

With a checkbox in A2 and a task label in B2, enter =IF(A2,"Complete","Pending") in C2. If A2 is checked, C2 displays Complete; if A2 is unchecked, it displays Pending. The text in quotation marks is the result shown in the cell, not a special checkbox command.

You can return numbers, dates, or other formula results instead of words. For instance, with a checkbox in A2, a number of points in B2, and a total in C2, enter =IF(A2,B2,0) in C2. When the box is checked, the formula returns the value in B2; when it is unchecked, it returns 0. This is useful for including only approved items in a total.

IF is not required if you only need to pass the checkbox state through to another cell: a formula that refers to A2 directly returns TRUE or FALSE. Use IF when you want a more readable label, a different value, or an action such as including a quantity only when checked.

Set up a checkbox-driven task list

For a simple checklist, put task names in column B, checkboxes in column A, and a status formula in column C. Assuming the first task is on row 2, place =IF(A2,"Done","Not done") in C2 and fill the formula down. Each row then refers to the checkbox beside it.

To calculate completion for several tasks, use a count of the checked boxes. With standard checkboxes in A2:A11, enter =COUNTIF(A2:A11,TRUE) in another cell. If three boxes are checked, the result is 3. To show the fraction complete, divide that count by the number of checkboxes; with the same layout, use =COUNTIF(A2:A11,TRUE)/COUNTA(B2:B11) when B2:B11 contains one task name for every checkbox. Format the result as a percentage if you want a percent display.

To display a friendly summary, combine the count with text. Assuming the same range and task-name layout, enter =COUNTIF(A2:A11,TRUE)&" of "&COUNTA(B2:B11)&" tasks complete". If three tasks are checked and ten task names are present, the result reads “3 of 10 tasks complete.” This approach is more reliable than counting all rows if your list has blank rows.

Use checkboxes in totals and conditional decisions

A checkbox can determine whether a row contributes to a calculation. Suppose A2:A6 contains standard checkboxes and B2:B6 contains prices. In C2, enter =IF(A2,B2,0) and fill it down to C6. Then, with the calculated amounts in C2:C6, enter =SUM(C2:C6) in the total cell. Only prices on checked rows contribute. You can also use a single conditional-sum formula when you do not need the per-row results.

With standard checkboxes in A2:A6 and prices in B2:B6, enter =SUMIF(A2:A6,TRUE,B2:B6) to add the prices on checked rows. If the checked rows have prices of 12 and 8, the result is 20. The checkbox range and the sum range must cover corresponding rows.

For a more complex decision, combine IF with AND or OR. With an approval checkbox in A2 and a completed-date cell in B2, use =IF(AND(A2,B2<>""),"Ready","Waiting") to display Ready only if the checkbox is checked and B2 is not empty. In HTML, <> represents the not-equal operator in the formula. This can help distinguish “approved” from “ready to process.”

Copy formulas down and handle blank rows

When you fill a formula down a column, ordinary cell references adjust by row: a formula that refers to A2 becomes one referring to A3 on the next row. This is usually what a checklist needs. To fill it down, select the formula cell and drag its small fill handle, or copy and paste it into the target cells.

Rows with no checkbox may be treated like FALSE in a simple IF test, which can cause a blank row to display “Not done.” If you want unused rows to remain blank, first test whether the task-name cell is empty. With task names in B2 and a checkbox in A2, enter =IF(B2="","",IF(A2,"Done","Not done")) in C2. A blank task name produces a blank result; otherwise the formula reflects the checkbox. Copy the formula down as needed.

When you copy a formula that uses a fixed range for a summary, consider whether that range should grow as tasks are added. A bounded range such as A2:A100 is easy to understand, while a whole-column reference can include future rows but may make a workbook less efficient in a large, complex sheet. Match the range to how the sheet is maintained.

Custom checkbox values change the formula

Google Sheets also allows custom values for checked and unchecked states. Select the checkbox cells and choose Data > Data validation; set the criteria to Checkbox and enable Use custom cell values, then specify the two values. The checked state may then store a value such as “Yes” rather than TRUE. In that case, a formula that expects TRUE may not match the checkbox value.

With a checkbox in A2 configured to store Yes when checked and No when unchecked, use =IF(A2="Yes","Complete","Pending"). The comparison must match the configured checked value. If the custom value is numeric, compare it as a number rather than putting it in quotation marks. Check the validation rule if you are unsure which values the checkbox stores.

Custom values can be useful when a sheet needs to feed another process that expects specific labels, but standard TRUE and FALSE values are usually simpler for formulas. Avoid changing checkbox values without updating dependent formulas, filters, or charts that rely on the earlier values.

Common problems and fixes

The formula displays the wrong status: Confirm that the formula points to the checkbox in the same row. A reference to A2 will not follow a checkbox in D2.
A checked box is not recognized as TRUE: Check whether the checkbox uses custom values. If it does, compare against the configured value instead.
The result is a number rather than a label: IF returns exactly the value specified in its true or false argument. Put text in double quotes, or reference a cell containing the desired result.
The formula shows an error: Check the parentheses, commas, quotation marks, and range sizes. Spreadsheet locale settings can affect formula separators; if commas are rejected, use the separator required by your locale.
Blank rows say “Pending”: Add an initial test for an empty task-name cell, as in the blank-row example above.

A common misconception is that a checkbox needs a special function. It does not: for a standard checkbox, the cell itself is already a Boolean condition. Another easy-to-miss distinction is that conditional formatting changes how a cell looks, while IF changes the value displayed by a formula. If you only want a row to change color when checked, choose Format > Conditional formatting and set a rule based on the checkbox value; if you need text or a calculated amount, use a formula.

Google Sheets versus Excel

The core IF pattern is familiar in both Google Sheets and Microsoft Excel: a standard checkbox represents a true-or-false state that a formula can use. However, checkbox insertion and configuration are not identical across spreadsheet programs and versions. The steps here use Google Sheets in a web browser, including its Insert > Checkbox and Data > Data validation menus.

In Excel for Microsoft 365 on Windows, checkbox availability and behavior depend on the checkbox feature or controls used in that workbook, so do not assume that Google Sheets menu instructions apply. Excel’s newer in-cell checkbox feature uses TRUE and FALSE values, but older form or ActiveX controls can work differently. When sharing a file between Sheets and Excel, verify that the checkbox cells and formulas still work after conversion rather than assuming the visual control will transfer unchanged.

Frequently Asked Questions

What formula checks whether a checkbox is checked?
For a standard checkbox in A2, use =IF(A2,"Yes","No"). It returns Yes when checked and No when unchecked.

Does a checked Google Sheets checkbox equal TRUE?
Yes, for a standard checkbox. A checkbox configured with custom values stores those values instead, so formulas should test for the configured checked value.

How do I count checked boxes?
For standard checkboxes in A2:A20, use =COUNTIF(A2:A20,TRUE). The result is the number of checked boxes in that range.

How do I add values only when a checkbox is checked?
With standard checkboxes in A2:A20 and amounts in B2:B20, use =SUMIF(A2:A20,TRUE,B2:B20). It adds the amounts corresponding to checked rows.

Can I keep the formula result blank when the task row is empty?
Yes. If task names are in B2 and the checkbox is in A2, use =IF(B2="","",IF(A2,"Done","Not done")). The formula leaves the result blank when B2 is empty.

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.