If you are using Excel to create a checklist, you can count the number of checked or unchecked checkboxes. With a simple formula, you can count them in a cell that adjusts as more fields are checked or unchecked.
Assigning Cells to Checkbox Controls
When you check a checkbox in Excel, the validation result is True. For unchecked checkboxes, the result is False.
So, before you create a formula to count your checkboxes, you need to assign cells to store the True or False result. You will then use this result in your formula.
Right-click the first checkbox and select “Format Control” from the context menu.
In the Manage Format window that appears, go to the Manage tab. In the Cell Reference field, enter the cell in which you want to display the True or False result. You can also select a cell on the worksheet to fill in this field.
Click OK to save the change.
Follow the same process for other checkboxes you want to count on your worksheet.
You should then see True for checked checkboxes and False for unchecked checkboxes in the marked cells.
Note. If you set the default value of the checkbox to “Unchecked”, it will not display the value “False” until you check the box and then uncheck it.
Use the COUNTIF function
Once you have checked the boxes, go to the cell where you want to display the quantity.
You will then enter a formula for the COUNTIF function, which displays a number for True or False, depending on what you want to count.
For example, we count the checked checkboxes in cells B2 through B11 using their results in cells C2 through C11. So, you will use result cells in your formula like this:
=COUNTIF(C2:C11,TRUE)
You can see that we got the correct number of 6 for our checkboxes.
To count unchecked checkboxes instead, simply replace True with False in the formula:
=COUNTIF(C2:C11,FALSE)
Note. If you set the default value of the checkbox to “Mixed”, it will not be taken into account when determining the result of “True” or “False”. It is displayed as #N/A until the checkbox is checked or unchecked.
Optional: hide result cells
Displaying True and False results in your worksheet may not be ideal. This can detract from the data you want to see.
If you have results in one column or row with no other data you need, you can just hide the column or row.
Right-click a column or row and choose Hide from the context menu.
The formula for checked or unchecked checkboxes will work exactly the same if the results are hidden.
Counting the number of completed tasks, pending orders, or the like is easy to do with the COUNTIF function and a little manipulation of checkboxes in Excel.
Leave a Reply