Are you ready to discover a quick and useful shortcut for summing selected values in Google Sheets? Let me explain how you can use checkboxes to get the sum of desired cells.
Adding a Checkbox
To get started, we need to add a checkbox. Select the cells where you want to insert the checkbox, then go to Insert and choose Checkbox from the options displayed.
Now you have inserted the checkboxes!
Combining Sum with Checkboxes
There is a QUICK method to combine the sum formula with checkboxes. This involves using the ARRAYFORMULA function. Before explaining how to use it, let’s do a quick demonstration. In Google Sheets, you can multiply numbers by the value of the checkbox. If the checkbox is checked, it will have a TRUE value, equivalent to 1. If the checkbox is unchecked, it will have a FALSE value, equivalent to 0. So, we need to multiply the product of the value by the checkbox value. To show you how the checkbox can act as a 0 or 1 value in a calculation, we have added column D, but you don’t need to add it to your own calculations.
Now, let’s finally add the ARRAYFORMULA function. To make it work correctly, you need to add it 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 need to be converted into individual rows using ARRAYFORMULA. The equation is now converted to:
=SUM(B2*C2, B3*C3, B4*C4, B5*C5)This form follows the syntax of the SUM function and can be processed, giving us the sum of the amounts with the checkboxes checked. The result is:
Really handy, isn’t it?
Optimize Your Spreadsheets with Lido
If you enjoyed this article, you will definitely like our article on how to sum a column in Google Sheets or our article on how to use the autosum function in Google Sheets.
If you want to get a copy of our Google Sheets contact list template, we also suggest checking out our detailed guide.
Easily track the deadlines of your spreadsheet with our deadline management software in just a few clicks.
Discover more tips and tricks on Crawlan.com.
