Let's say we made a list where we can select which values to add to get their sum:
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.
We have now inserted the checkboxes.
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.
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:
Quite neat, right?
Supercharge Your Spreadsheets with Lido
Recommended articles
More resources
