How to Count Checkboxes in Microsoft Excel

How to Count Checkboxes in Microsoft Excel

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.

Format control in 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.

Cell reference in the Cell Reference field

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.

Checkboxes displaying True

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.

COUNTIF True for checkboxes

To count unchecked checkboxes instead, simply replace True with False in the formula:

=COUNTIF(C2:C11,FALSE)

COUNTIF False for unchecked fields

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.

Hide in column menu

The formula for checked or unchecked checkboxes will work exactly the same if the results are hidden.

Result column is hidden in Excel

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.

How to display all rows in Excel

How to display all rows in Excel

Displaying all rows in a Microsoft Excel spreadsheet is as easy as pressing a keyboard shortcut or using a button on the ribbon. We’ll show

2022/11/19

Leave a Reply

Your email address will not be published.