In this tutorial, you will learn how to sum all numbers in a range that have a checked checkbox in Google Sheets.
How to Sum If Checkbox is Checked in Google Sheets
Quick Navigation
Checkboxes in Google Sheets can be a great way to make your spreadsheet more interactive.
But did you know you can also use the checkboxes to control how your sheet performs calculations?
We can use the checkboxes in Google Sheets to determine which cells to include in a sum. For example, you may have a tracker of expenses for an event and you want to use checkboxes to indicate whether each expense has been validated.
We can even set up a formula that checks two different checkbox columns to determine what values to include in the total.
In this guide, we will show you how to find the sum of all values where the corresponding checkbox is checked in Google Sheets.
We’ll cover how to use the SUMIF formula to add all values in a range given that the corresponding value in another cell is a checked checkbox. We will also look into a similar method using SUM and ARRAYFORMULA.
How to Find the Sum of All Checked Values in A Range in Google Sheets
Here’s how to find the sum of all checked values in a range in Google Sheets.
Step 1
We’ll need to create a new column that will hold our checkboxes.
In our example above, we want to find the sum of the amounts of particular expenses. The checkboxes in column C will determine which expenses are included in the final sum.
Step 2
We can add checkboxes to a selected range by clicking on Insert > Checkbox.
Step 3
Use your cursor to check off the corresponding checkbox elements of each row you want included in the sum.
Step 4
In a new blank cell, we’ll start typing the SUMIF function.
Step 5
The first argument of the SUMIF function will be our range of checkboxes.
Step 6
Next, we’ll set the criteria argument to TRUE.
This will ensure that only rows with a checked checkbox will be added in the total.
Step 7
The last argument will refer to the range we will sum up.
Step 8
Modifying which checkboxes are selected will automatically update the result of the SUMIF function.
Step 9
We can also use the SUM and ARRAYFORMULA functions to achieve a similar result.
Multiplying the two ranges using the ARRAYFORMULA essentially cancels out all amounts with a corresponding unchecked checkbox.
This is because Google Sheets considers the FALSE value equal to 0 when multiplying.
Step 10
We can even add additional checkbox columns to further specify which values to include in the final total.
In the example above, the expense amounts are only included if they are checked on columns C and D.
Summary
This guide should be everything you need to sum all values in a range with a corresponding checked checkbox in Google Sheets.
You may make a copy of this example spreadsheet to test it out on your own.