Sum if Checked

Today, I’ll tackle a question I received recently about checkboxes. The question was essentially about how to add two values in Excel when a checkbox is checked. I’ll walk though the answer with you in this short post. Let’s jump right in!

Video

Step-by-Step Guide

Exercise 1: Getting Started with Checkboxes

To get started, we need to learn the basics of adding checkboxes in Excel. Here’s a simple way to do it:

1. Select the Range: Begin by selecting the range of cells where you want to add checkboxes.
2. Insert Checkboxes: Go to the Insert tab and choose Checkbox from the Controls group.

The checkbox command presented above is not available in all Excel versions. So, depending on when you read this and depending on your Excel version, you may not have the Insert > Checkbox control command.

Once the checkboxes are inserted, there are a few important points to keep in mind:

• Using the Mouse: You can easily check or uncheck the checkboxes by clicking on them with your mouse.
• Using the Keyboard: Keep your hands on the keyboard! You can use the Space key to check or uncheck checkboxes. If you select a range of cells and press Space, all checkboxes within that range will toggle.
• Delete Key Functionality: Pressing the Delete key when the checkbox is checked will uncheck it. Pressing it on an unchecked box will delete the checkbox altogether.

These keyboard shortcuts can help streamline your workflow, making it much more efficient to manage checkboxes in Excel.

Understanding the Stored Value

Now, here’s where things get interesting. Although the checkbox visually appears as a simple tick box in the cell, the underlying stored value is actually a Boolean value—either TRUE or FALSE.

• When the checkbox is unchecked, the stored value is FALSE.
• When the checkbox is checked, the stored value changes to TRUE.

You can see this in action by selecting a cell with a checkbox and looking at the formula bar.

This Boolean value is what enables us to perform more advanced operations, which we’ll explore next.

Exercise 2: Adding Values When Checkbox is Checked

Imagine this scenario: You have a customer list with items, prices, installation fees, and totals. Sometimes, you want to include the installation fee of $100 in the total, and sometimes you don’t. Here’s how you can use checkboxes to manage this:

Basic Setup:

• Cell D6 contains a checkbox.
• Cell D8 contains the price.
• Cell D9 contains a formula that will return 100 if the checkbox is checked.
• Cell D10 will display the total cost (Price + Installation).

Formula Explanation:

• The formula in D9 will look something like this:
  =D6*100

Here’s the magic: when the checkbox in D6 is unchecked (FALSE), it is treated as 0 by Excel’s calc engine. So, when it is unchecked, the formula returns 0 for the installation line (0*100 = 0). When the checkbox is checked (TRUE), it is treated as a 1 by Excel’s calc engine, and the formula returns an installation fee of 100 (1*100 = 100).

Thus, the total dynamically changes based on whether the checkbox is checked or not!

Note: Since this is Excel, of course there are other ways to set up the formula to return 100 when the checkbox is checked, including =IF(D6,100,0). Feel free to use an alternative based on personal preference. They key is that the checked cell stores TRUE/FALSE which can be referenced in your formula, or, you can also leverage the fact that Excel’s calc engine also treats these as 1/0.

Exercise 3: Creating a Running Total with Checked Items

In this exercise, let’s take it a step further. We want to calculate a running total based on which items have been checked. This is a great way to keep track of selected items in a list.

For example, we have a list of possible items we can pick from:

Let’s say we have a bunch of Battle Stars to spend on these different items. We’d like our workbook to track how much we spend as we check each item.

To sum the total amount for the checked items, we’ll use the SUMIFS function.

• The SUMIFS function allows us to sum values in a range based on a condition.
• Our condition here is that the checkbox cell must be TRUE (checked).

In this case, we want to add the values in column C when the value in column D is TRUE (that is, when the checkbox is checked). The formula would look like this:

=SUMIFS(C11:C23,D11:D23,TRUE)

This formula (in cell C25) sums up all the values in column C where the corresponding checkbox in column D is checked (TRUE).

Conclusion

And there you have it! Checkboxes in Excel are more than just visual tools; they can help perform dynamic calculations and streamline your workflow. We explored how to add values when checkboxes are checked, and calculate running totals using this simple yet powerful feature.

I hope you found this guide helpful. If you have any questions or need further clarification, please drop them in the comments below. Happy Excel-ing!

Sample File

To help you practice, I’ve prepared a sample Excel file with all the exercises we covered.