SUMIF Checkbox in Google Sheets: Sum Checked Rows

If your checkboxes are in A2:A20 and the values to add are in B2:B20, use =SUMIF(A2:A20,TRUE,B2:B20) to total the values on checked rows. Google Sheets checkboxes normally contain TRUE when selected and FALSE when not, so TRUE is the criterion for adding checked rows.

What SUMIF Does With a Checkbox

SUMIF adds values from one range when corresponding cells in another range meet a condition. Its arguments are the range to test, the condition, and the range to sum. For checkbox totals, the checkbox cells are the range to test, TRUE is the condition, and the amounts or other numbers are the sum range.

The formula checks each cell in A2:A20. When a cell is TRUE, Sheets adds the value from the same row in B2:B20. When a checkbox is FALSE, that row’s value is left out. The checkbox itself does not need to contain a number: it supplies the condition that determines which numbers are included.

Keep the two ranges aligned and the same size. If the first checkbox is in A2, its associated value should be in B2; the next checkbox in A3 should be paired with B3, and so on. A shifted or differently sized range can produce an incorrect result or a formula error.

How Google Sheets Checkbox Values Work

A standard Google Sheets checkbox represents a logical value. A checked box is TRUE, and an unchecked box is FALSE. These are Boolean values, not the text strings “TRUE” and “FALSE.” That distinction matters when writing a formula: use TRUE without quotation marks for a standard checkbox criterion.

To add checkboxes in a browser, select the cells and choose Insert > Checkbox. You can then tick or clear each box, or copy a checkbox into additional cells. A checkbox is useful when a person needs to mark a status directly, such as whether a task is done, an order is approved, or an expense is reimbursable.

Checkboxes can also be configured with custom checked and unchecked values. In that case, the cell may contain text such as “Yes” and “No” instead of TRUE and FALSE. The formula’s condition must match the value actually stored in the checked cells. The checkbox’s visual appearance alone does not tell SUMIF what criterion to use.

Set Up a Simple Checked-Row Total

For a basic tracker, put a checkbox heading in column A and a numeric amount heading in column B. Add checkboxes in A2:A20 and amounts in B2:B20. Then put the total formula in a separate cell, such as B22, so it does not overlap the data being summed.

Select A2:A20, then choose Insert > Checkbox.
Enter a number in B on each row you want to track. Leave the checkbox clear until that row should count.
In the result cell, enter =SUMIF(A2:A20,TRUE,B2:B20).
Check and uncheck a few boxes to confirm that the total changes as expected.

For example, suppose rows 2 through 5 have amounts of 12, 7, 18, and 5. If rows 2, 4, and 5 are checked, the result is 12 + 18 + 5, or 35. The unchecked row’s 7 is excluded. If you check that row too, the displayed total becomes 42.

Worked Example: A Task or Expense Tracker

Imagine a small expense sheet with checkboxes in A2:A5 and expense amounts in B2:B5. The amounts are 12, 7, 18, and 5 dollars. If the first, third, and fourth expenses have been approved, the formula returns 35. This can be useful for a running total of approved items while keeping pending entries visible in the same list.

To count how many rows are checked rather than add their amounts, use COUNTIF instead. With checkboxes in A2:A20, =COUNTIF(A2:A20,TRUE) returns the number of selected boxes. If three boxes are checked, the result is 3, whether the associated amounts are small, large, or blank. SUMIF answers “what is the total value?”; COUNTIF answers “how many rows meet this condition?”

These formulas do not change the checkbox state. They read the cell values and calculate a result, so users can continue to update the checkboxes normally. If the sheet is shared, collaborators who have permission to edit can change the boxes and the total will recalculate.

Use Custom Checkbox Values

If your checkboxes use custom values, use the checked value as the SUMIF criterion. For example, a checkbox configured to store Yes when checked and No when unchecked needs a text criterion. Text criteria in formulas use quotation marks, unlike the Boolean TRUE criterion used by a standard checkbox.

Assume A2:A10 contains custom checkboxes that store Yes or No, and B2:B10 contains amounts. If the checked rows have amounts of 16 and 6, =SUMIF(A2:A10,"Yes",B2:B10) returns 22. If the formula returns zero, check the checkbox settings and inspect a cell’s actual value; the stored value might not be the one you expected.

In Google Sheets, checkbox options can be reviewed or changed through Data > Data validation. Select the checkbox rule and inspect its checked and unchecked values. If you change existing values or the rule after formulas are in place, make sure the formula criterion still matches the checked value exactly.

Sum Checked Rows That Meet Another Condition

Sometimes a checkbox is only one requirement. For example, you may want to total checked items for one department or category. SUMIFS supports multiple conditions. Its first argument is the sum range, followed by pairs of criteria ranges and criteria. The checkbox condition and the category condition must both be true for a row to contribute.

Assume A2:A100 contains department names, B2:B100 contains standard checkboxes, and C2:C100 contains amounts. To total checked items for North, enter =SUMIFS(C2:C100,B2:B100,TRUE,A2:A100,"North"). If the North rows include checked amounts of 20 and 9, plus an unchecked amount of 4, and other departments are excluded, the result is 29.

For a single condition, SUMIF is shorter and easier to read. Use SUMIFS when you need two or more tests, such as checked status plus a particular project, date range, team, or category. Make sure each criteria range covers the same rows as the sum range.

Common Problems and How to Fix Them

The result is zero: Confirm the boxes are checked and that the formula uses the right criterion. Standard checkboxes use TRUE; custom checkboxes may use a word or number instead.
The total is too high: Check whether unchecked rows are actually storing a custom value that matches your criterion, and verify that the sum range points to the intended numeric column.
The formula gives an error: Compare the start and end rows of each range. Checkbox and sum ranges should cover corresponding rows and have matching dimensions.
Numbers do not add as expected: Confirm that the values to sum are numbers, not text that only looks like numbers. Also check that the formula is not including a total row within its own sum range.
TRUE seems not to match: Standard checkbox values are Boolean. Typing the text “TRUE” into a cell is not necessarily the same as using a checkbox value, and custom checkbox settings can change what is stored.

Use a small test area if you are unsure what a checkbox contains. Select a checkbox cell and examine its value in the formula bar, or temporarily test a known checked and unchecked row. Avoid replacing the checkbox with typed text just to make a formula work; first confirm the checkbox’s configured values and adjust the criterion accordingly.

Filters, Hidden Rows, and Excel Differences

SUMIF evaluates its ranges; it does not automatically restrict the calculation to rows currently visible after a filter. If a checked row is hidden by a filter, its amount can still be included in the total. This is a common source of confusion when someone expects the result to reflect only the rows on screen. If the total needs to respond to visibility, use a method designed for visible rows rather than assuming SUMIF follows the filter.

Sorting the entire table is generally compatible with the formula because the checkbox and amount stay together when their rows move. Sorting only one column, however, can separate checkboxes from the values they were meant to control. Select the full data range when sorting, and consider protecting formula cells to reduce accidental edits.

Excel uses the same SUMIF logic and formula syntax when checkbox cells contain TRUE or FALSE. The difference is how checkboxes are created: Excel’s in-cell checkbox feature stores a logical value, while older floating form-control checkboxes are separate objects and may need a linked cell. Google Sheets checkboxes are inserted directly into selected cells with Insert > Checkbox.

Frequently Asked Questions

Can SUMIF add only checked boxes in Google Sheets?
Yes. For standard checkboxes, use TRUE as the criterion and put the amounts in the sum range. The formula adds a row’s amount only when its corresponding checkbox is checked.

Do I put TRUE in quotation marks?
Not for a standard checkbox. Use TRUE without quotation marks because the checkbox stores a Boolean value. Use quotation marks when the checked value is text, such as Yes.

How do I count checked boxes instead of summing values?
Use COUNTIF on the checkbox range with TRUE as the criterion. COUNTIF returns the number of checked boxes rather than adding an adjacent column.

Why does my checkbox SUMIF return zero?
Common causes include using the wrong checked value, including the wrong ranges, or having amounts stored as text. For custom checkboxes, inspect the checked value in Data > Data validation and use that exact value as the criterion.

Does SUMIF ignore rows hidden by a filter?
No. A standard SUMIF can include matching rows even when a filter hides them. Filtering changes which rows are displayed, not the basic way SUMIF evaluates its ranges.

Can I use this with more than one condition?
Yes. Use SUMIFS when the total should depend on checkbox status and another condition, such as a department or category. Add a criteria-range and criterion pair for each additional requirement.

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.