How to Count Checkboxes in Microsoft Excel

Microsoft Excel logo on a green background

First, pick cells that contain “True” or “False” results based on the status of each check box. You can then add up the check boxes by using Excel’s COUNTIF function to count the number of “TRUE” results.

If you use Excel to create a checklist, you may want to count the number of checked or unchecked boxes. With a simple formula, you can sum them up in a cell that adjusts as more boxes are marked or unmarked.

Hover cells for the check boxes

When you check a box in Excel, the result of the check is True. For unchecked boxes, the result is False.

So, before you create the formula to count your checkboxes, you need to designate cells to contain True or False result. You then use that result in your formula.

RELATED: How to Count Checkboxes in Google Sheets

Right click on your first checkbox and select “Format Control” from the shortcut menu.

Format controls in the shortcut menu

In the Format Control box that appears, go to the Controls tab. In the Cell Link box, enter the cell in which you want to display the True or False result. You can also select the cell in your sheet to fill that box.

Click “OK” to save the change.

Cell reference in the Cell Link box

Follow the same process for the other checkboxes you want to count in your sheet.

You should then see the result True for checked boxes and False for unchecked boxes in the designated cells.

Checked boxes with True

Remark: If you set the default value for the check box to Unchecked, False will not be displayed unless you check the check box and then clear it.

Use the COUNTIF function

Once you leave the checkboxes set, move to the cell where you want to display the count.

Then you enter a formula for the COUNTIF function that displays a count for True or False, depending on what you want to count.

RELATED: How to use the COUNT function in Microsoft Excel?

As an example, we will count the checked boxes in cells B2 through B11 using their results in cells C2 through C11. So you use the result cells in your formula as follows:

=COUNTIF(C2:C11,TRUE)

You can see that we have received the correct count of 6 for our ticked boxes.

COUNTIF True for checked boxes

To count the unchecked boxes instead, just replace True with False in the formula:

=COUNTIF(C2:C11,FALSE)

COUNTIF False for unchecked boxes

Remark: If you set the default value for the check box to Mixed, it will not count towards the True or False result. It will be displayed as #N/A until the box is checked or unchecked.

Optional: hide the result cells

It may not be ideal to display the True and False results in your sheet. It can distract from the data you want to see.

If you have the results in a single column or row with no other data you need, you can easily: hide the column or row.

RELATED: How to Hide Cells, Rows and Columns in Excel

Right-click the column or row and choose “Hide” from the shortcut menu.

Hide in the column menu

The formula for the checked or unchecked boxes works just the same with the results hidden.

Results column hidden in Excel

Counting the number of completed tasks, incomplete orders, or the like is easy to do with the COUNTIF function and a bit of checkbox manipulation in Excel.

For more information, see how to use check boxes in your Word documents as well.

Add Comment