So you have created a form in Excel with nifty Check Box Controls. Looks real purrrty! But how do you determine how many check boxes have been selected? Here follows a simple example of how to achieve this!
- Select the Developer tab
- Add the Check Box controls from the Insert Controls button
- Place the checkboxes on the spreadsheet
- Select cell C4
- Create a COUNTIF function that counts the cells in the range B1:B3 if they are TRUE
The next step is to link each checkbox control to a cell. Select the control — this seems impossible at first as left-clicking the control simply checks the checkbox (as one would expect).
- Right-click the control and
- select the Format Control option.
There are a number of properties available, but for now we will just focus on the key one which is the option to link the control to a cell:
- Select the Control tab
- Click in the Cell link option and select the cell you want to link this control to: B1
- Click OK
Follow the same steps for the additional checkboxes linking them to cells B2 & B3 respectively.
- When you select the checkbox in cell C1
- it updates the cell you linked it to (B1) with the value TRUE
- which in turn updates the calculation in cell C5.
Test yourself to see what happens when you deselect checkboxes: the value is set to FALSE for a de-selected checkbox. Just remember that the linked cell has no value (it is null) prior to it being selected and that you can delete the value from the linked cell without changing the state of the checkbox.
As a final step, I would hide column B which really only serves as a means to an end!