To add only the values whose checkboxes are checked, put each checkbox’s TRUE/FALSE value in one column and the amounts in another, then use =SUMIF(B2:B10,TRUE,C2:C10). Here, B2:B10 contains the checkbox values and C2:C10 contains the amounts; SUMIF adds the amount on each row where the checkbox is TRUE.
How Excel checkboxes work
A checkbox is a control for recording a choice: checked means TRUE, and unchecked means FALSE. A formula cannot generally inspect the visual state of a floating checkbox object directly. It needs a cell value representing that state. Excel’s newer in-cell checkboxes store TRUE or FALSE in the cells themselves; older Form Control checkboxes can write their state to a linked cell.
Once the state is available in a worksheet cell, ordinary Excel functions can use it. SUMIF is a natural fit when each row has one checkbox and one corresponding value. It tests the checkbox-value range and sums values from the matching rows. The checkbox is an input, while the formula calculates the total.
Choose the checkbox type
For a new workbook in Microsoft 365, an in-cell checkbox is usually the simplest option. Select the cells where you want checkboxes and choose Insert > Checkbox. Each cell displays a checkbox and contains TRUE when selected or FALSE when cleared. You can use those cells directly in formulas without adding a separate link column.
In older Excel versions, or in a workbook already built with floating controls, use a Form Control checkbox and link it to a cell. On Windows, if the Developer tab is not visible, enable it through File > Options > Customize Ribbon and select Developer. Then choose Developer > Insert > Check Box in the Form Controls area, draw the control, and open its context menu to choose Format Control. On the Control tab, set Cell link to a worksheet cell. That linked cell reports TRUE or FALSE as the box is checked or cleared.
For example, a Form Control beside row 2 might link to B2, while the amount for that row sits in C2. Repeat with a different linked cell for every checkbox. The link cells can be placed in a helper column and hidden after the formula is working, but keeping them visible while setting up the sheet makes errors easier to spot.
Write the SUMIF formula
Suppose the linked checkbox values are in B2:B10 and the amounts are in C2:C10. Enter this formula in the cell where you want the selected total:
With checkbox states in B2:B10 and amounts in C2:C10, enter =SUMIF(B2:B10,TRUE,C2:C10). If rows 2, 4, and 7 are checked and their amounts are 12, 25, and 9, respectively, the result is 46.
The arguments have distinct jobs: B2:B10 is the range Excel checks, TRUE is the condition, and C2:C10 is the range to add. The criteria range and sum range should have the same dimensions and row alignment. If they do not, the formula may not associate each checkbox with the intended amount.
For in-cell checkboxes, use the checkbox cells themselves for B2:B10. For Form Controls, use the linked-cell range. The formula is identical in either case as long as the cells contain logical TRUE and FALSE values. It updates when a checkbox changes; you do not need to recalculate the total by hand.
Build a small working example
A simple table makes the relationship between each choice and each amount clear:
- Column A: task or item name.
- Column B: checkbox, or the linked cell for its Form Control checkbox.
- Column C: the number or amount to include.
- Cell E2: the SUMIF total.
With names in A2:A5, checkbox values in B2:B5, amounts in C2:C5, and the total in E2, enter =SUMIF(B2:B5,TRUE,C2:C5) in E2. If B2, B4, and B5 are TRUE and the corresponding values in C2:C5 are 8, 14, 6, and 3, Excel returns 17: it adds 8, 6, and 3, while excluding the unchecked row’s 14.
This layout also helps prevent a common modeling mistake: putting a single checkbox beside the total and expecting it to control many unrelated values automatically. A checkbox has to represent the inclusion decision for the row or group it governs. If one checkbox should include a whole block of values, create a formula for that specific design or use a clearly defined helper column; do not assume a lone checkbox will infer which rows you mean.
Sum by more than one condition
SUMIF handles one condition, such as checked or unchecked. When a total must also depend on another field—for example, include checked expenses only for the category Travel—use SUMIFS. It allows multiple criteria ranges, with each criterion applying to its corresponding rows.
With checkbox states in B2:B20, categories in A2:A20, and expenses in C2:C20, enter =SUMIFS(C2:C20,B2:B20,TRUE,A2:A20,"Travel"). This adds expenses only where the checkbox is TRUE and the category is Travel. In SUMIFS, the sum range comes first, followed by each criteria range and its condition.
Another useful setup is a checkbox per line item where the values are quantities, hours, or points rather than currency. SUMIF works the same way: it adds numeric values for checked rows. The unit and meaning come from the data, not from the checkbox or the function.
Use the formula that fits the layout
SUMIF is generally the clearest choice when one Boolean checkbox column determines which values are included. For one checkbox paired with one amount, however, an IF formula may be easier to read. It returns the amount when checked and zero otherwise, which is useful when you want a per-row included-value column as well as a total.
With a checkbox value in B2 and its amount in C2, enter =IF(B2,C2,0) in D2 and copy it down through the item rows. If B2 is TRUE and C2 is 12, D2 returns 12; if B2 is FALSE, it returns 0. You can then sum the resulting column. This approach exposes each row’s contribution, while SUMIF produces the total directly.
Do not use a text test such as the word checked unless the cells actually contain that text. Checkboxes normally produce logical TRUE/FALSE values, not the strings “checked” and “unchecked.” Testing the logical value avoids confusion between Boolean values and text that merely looks similar.
Common problems and fixes
- The total stays at zero: Confirm that the checkbox is linked to the cell you included in the criteria range, and that the linked cell changes between TRUE and FALSE when you click it.
- The formula includes the wrong rows: Check that each checkbox link and amount occupy corresponding rows. A one-row offset can produce a plausible but incorrect total.
- The formula shows an error: Verify the range references and commas. In some regional settings, Excel uses semicolons as argument separators; use the separator expected by your Excel installation if the comma version is rejected.
- Some amounts are ignored: SUMIF adds numeric values. Amounts stored as text may not be included as expected, so check the cells’ underlying values and convert text-formatted numbers to numbers when appropriate.
- Checkbox objects are difficult to select: A Form Control sits above the grid rather than being the cell value itself. Use its linked cell for the formula, or consider in-cell checkboxes for a simpler worksheet layout.
When debugging, temporarily display the linked cells and click each checkbox. Seeing TRUE and FALSE appear is a quick way to separate a checkbox-link problem from a formula-range problem. Also check for blank rows, duplicate controls linked to the same cell, or amounts placed outside the sum range.
Excel for the web and Mac considerations
Excel for the web and Excel for Mac may not present the same checkbox tools as Excel for Microsoft 365 on Windows, and availability can depend on the version and update channel. In-cell checkbox availability is not universal across every older Excel release. If you do not see Insert > Checkbox, use a version that supports the feature or use a Form Control workflow available in your desktop edition.
On Mac, ribbon and dialog details for legacy controls can differ from Windows, so do not rely on the Windows keyboard shortcut or assume the same Developer-tab configuration steps. The formula itself is the same. If a workbook is shared across platforms, test that the checkboxes remain interactive and that their linked or in-cell values still update in the versions used by collaborators. Excel for the web can calculate formulas even when a particular desktop control is not convenient to edit there.
Frequently Asked Questions
Can Excel sum values when a checkbox is checked?
Yes. Make sure each checkbox produces a TRUE/FALSE value in a cell, then use SUMIF with TRUE as the condition and the amounts as the sum range.
What formula sums checked boxes?
With checkbox states in B2:B10 and values in C2:C10, use =SUMIF(B2:B10,TRUE,C2:C10). It adds the values on rows where the corresponding checkbox state is TRUE.
Do I need a linked cell for every checkbox?
For floating Form Control checkboxes, link each control to a cell so Excel can expose its state to the formula. In-cell checkboxes store TRUE or FALSE in their own cells, so they do not need separate linked cells.
Can I sum checked boxes in different categories?
Yes. Use SUMIFS to require both a TRUE checkbox value and a category match, or use separate SUMIF formulas for each category. Keep the ranges aligned so every condition refers to the same record.
Why does my checkbox formula return zero?
Usually the criteria range is not the range containing the actual Boolean values, the controls are linked to the wrong cells, or the referenced rows do not line up with the amounts. Inspect the linked cells and compare the formula’s ranges.
Will the total change when I click a checkbox?
Yes, when the checkbox changes its cell value and Excel is calculating normally. SUMIF recalculates from the updated TRUE/FALSE values, so the total changes without manually editing the formula.
Related
- Excel Not Summing Numbers? Causes and Fixes
- Excel Not Showing All Rows? Find and Restore Hidden Rows
- Excel Daylight Saving Time: How Dates and Hours Work
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula to Remove Duplicates From One ColumnIn Excel for Microsoft 365 or Excel 2021, enter =UNIQUE(A2:A100) in an empty cell to return a live…
- Excel XLOOKUP Function Examples for Everyday LookupsIn Excel for Microsoft 365, XLOOKUP finds a value in one range and returns the corresponding value from…
- Excel Formula for the Greater of Two Values: MAX or IF?To return the greater of two numeric values in Excel, use =MAX(A2,B2), assuming the values are in cells…
- SUMIFS Excel: Sum Values That Meet Multiple CriteriaSUMIFS is Excel’s conditional-sum function: it adds values only when all the conditions you specify are met. With…
- VLOOKUP in Excel: Steps, Examples, and Common FixesTo use VLOOKUP in Excel, put the value you want to find in a cell, then enter a…
- Concat Excel Columns: Formulas, Methods, and Fixes ExplainedTo concat Excel columns—that is, join values from separate cells into one—use the ampersand operator. With first names…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.