Blog
>
Tutorials

How to Sum Cells with Checkboxes in Google Sheets

Inserting checkboxes into your Google Sheets spreadsheet allows you to use the SUM formula only on checked checkboxes, giving users freedom to choose which numbers to include in the calculation.

3 Minutes

Let's say we made a list where we can select which values to add to get their sum:

Original list

How can we do so? We can add a checklist in Column C and then modify our formula so that the checkboxes will have an effect on the sum. Read on below to learn how to use this quick and useful shortcut..

How to add a checkbox

To add a checkbox, select the cells where you want to add it, and then go to Insert and select Checkbox on the options displayed. 

Insert then checkbox option highlighted

We have now inserted the checkboxes.

Checkboxes inserted on the selected column

Combining SUM with checkboxes

There is a QUICK way to combine the SUM formula with checkboxes. This involves using the ARRAYFORMULA. Before we go with it, let us do something first. In Google Sheets, you can multiply numbers with whatever is the value of the cell with the checkbox. The checkbox, if ticked, will have a TRUE value, which is equivalent to 1. If the checkbox is unticked, it will have the FALSE value, which is equivalent to 0. Therefore, we need to multiply the product of the value and that of the checkbox. To demonstrate how the checkbox can serve as a 0 or 1 value in a calculation , we added column D, but it is not necessary to add this column to your own calculations.

The value of a number can be multiplied with a checkbox in Google Sheets

We now finally add the ARRAYFORMULA. To work effectively, you must add the ARRAYFORMULA inside the SUM function. Our final formula is:

=sum(arrayformula(B2:B5*C2:C5))

As you can see, B2:B5 and C2:C5 are arrays. Multiplying B2:B5*C2:C5 will not work; they must be converted to individual rows using an ARRAYFORMULA. The equation is now converted into:

=SUM(B2*C2, B3*C3, B4*C4, B5*C5)

This form follows the syntax of the SUM function, and can therefore be processed, giving us the sum of those amounts with ticked checkboxes. The result is:

SUM plus ARRAYFORMULA and checkbox formula added. 

Quite neat, right?

Supercharge Your Spreadsheets with Lido

🚀 Import data from anywhere and build custom tools and powerful dashboards straight from your spreadsheet. Discover what's possible in our Build Gallery.

Turn your spreadsheet into software