We’ll use the following data set, which contains 3 columns (Name, ID, and Attendance) to count the checkboxes.
Method 1 – Using Properties and the COUNTIF Function to Count Checkboxes in Excel
Part 1.1 – Inserting Checkboxes
Let’s start with a simple dataset that has two columns named Name and ID.
Steps:
- Select a cell where you want to include a Checkbox. We have selected cell D5.
- Go to Developer Ribbon.
- From the Insert tab, choose Checkbox under Form Controls.
- Drag the Mouse Pointer.
- This creates the Checkbox.
- Edit the text adjacent to the Checkbox. We have simply removed the text.
Part 1.2 – Using the Properties Feature to Link the Cells
Steps:
- Right-click on the Checkbox for selection.
- From the Developer tab, go to Properties.
- A dialog box named Format Control will appear.
- Go to the Control menu.
- Click on the Selection Arrow of Cell link.
- Choose the cell where you want to keep the status of the Checkbox. We have selected the E5 cell.
- Click on the Selection Arrow to go back to the dialog box.
- Click on OK to get the result.
If the Checkbox is checked then it will show the Status as TRUE. If you uncheck the Checkbox after linking up the cell, then it will show the status as FALSE.
- Link all the Checkboxes individually to cells. You will see the following output.
Part 1.3 – Use the COUNTIF Function to Count the Boxes
- Select a cell where you want to keep the result. We have selected a merged cell from F5:F9.
- Use this formula in the F5:F9 cell:
COUNTIF will count the number if it fulfills the given conditions. E5:E9 is the range that we want to count. TRUE is the criteria.
- You can see the total number of checked Checkboxes.
Method 2 – Using the Context Menu Bar and the COUNTIF Function
Steps:
- Right-click on a Checkbox.
- From the Context Menu Bar, choose Format Control.
- A dialog box named Format Control will appear.
- Go to the Control Menu.
- Click on the Selection Arrow of Cell link.
- Choose the cell where you want to keep the status of the Checkbox. We selected the E5 cell.
- Click on the Selection Arrow to go back to the dialog box.
- Click on OK to get the result.
- You will see the status of that cell.
- Link all the Checkboxes individually.
- Select a cell where you want to keep the result. We have selected a merged cell from F5:F9.
- Use the corresponding formula in the F5:F9 cell.
Here, in this function, COUNTIF will count the number if it fulfills the given conditions. E5:E9 is the range that we want to count. TRUE is the criteria. So, in the E5:E9 range, if there is any TRUE, then it will count that.
- You can see the total number of checked Checkboxes.
Read More: If Checkbox Is Checked Then Apply Formula in Excel
Method 3 – Use VBA Code to Count Checkboxes in Excel
Steps:
- Choose the Developer tab and select Visual Basic.
- From the Insert tab, select Module.
- Copy the following Code in the Module.
Option Explicit
Sub CountCheckbox()
Dim Chkbx As CheckBox
For Each Chkbx In ActiveSheet.CheckBoxes
Chkbx.LinkedCell = Chkbx.TopLeftCell.Offset(0, 1).Address
Next
End Sub
Code Breakdown
- We have created a Sub Procedure named CountCheckbox.
- Next, we have declared a variable Chkbx as CheckBox to call the CheckBox.
- We used a For Each Loop to link all the Checkboxes to cells which will return the Status.
- Save the code then go back to Excel File.
- From the Developer tab, select Macros.
- Select the Macro named CountCheckbox and click on Run.
- You can see the Status result.
- Select a cell where you want to keep the result. We have selected a merged cell from F5:F9.
- Use the corresponding formula in the F5:F9 cell.
- You can see the total number of checked Checkboxes.
Read More: VBA to Check If CheckBox Is Checked in Excel
Things to Remember
- The use of the COUNTIF function for all three methods is the same. The basic difference between those methods is about linking the Checkboxes to the cells.
- The 3rd method for the linking process if fastest since it’s done for all cells at once.
Practice Section
You can practice the explained method by yourself.
Download the Practice Workbook
Related Articles
- How to Align Checkboxes in Excel
- How to Add Checkbox in Excel without Using Developer Tab
- How to Link Multiple Checkboxes in Excel
- How to Group Checkboxes in Excel
- How to Filter Checkboxes in Excel
- How to Apply Conditional Formatting Using Checkbox in Excel
- How to Remove Checkboxes from Excel
- How to Resize Checkbox in Excel
<< Go Back to Excel CheckBox | Form Control in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
Great job Musiha!
Unfortunately, Microsoft didn´t notice that AI is knocking on the door.
a) this function is not able to calculate the checked boxes and requires another column with same information (Checked=TRUE, Unchecked= FALSE)
b) is not able to provide Format Control Linking in array (all in one step). We have to link all the Checkboxes individually! Can Microsoft imagine to proivde table with more than hundred atendees?
Have a nice day
Michal
Greetings Michal,
Thanks for your comment! I understand the dissatisfaction with the limitations that you’ve faced. I agree that Microsoft could do more to enhance the functionality of their checkboxes.
Regarding your first issue, it is true that the COUNTIF function is unable to directly count the number of checked boxes.
Your second point—that Microsoft does not support Format Control Linking in an array—is also true. This implies that each checkbox needs to be linked separately. This process can take a while, especially if there are a lot of checkboxes.
However, we can do Format Control Linking using VBA code which we already mentioned in this article. Besides, we are adding another code that works as a function and dynamically does format control linking without any helper column and counts the checked boxes.
To work with this code, go to the Developer tab, and select Visual Basic. Now, from the Insert tab >> you have to select Module. Write down the following Code in the Module.
Public Function CheckBoxCount()
Dim checkBox As Shape
Dim count As Long
count = 0
With ThisWorkbook.ActiveSheet
For Each checkBox In .Shapes
If InStr(1, checkBox.Name, “Check Box”) Then
If .Shapes(checkBox.Name).OLEFormat.Object.Value = 1 Then
count = count + 1
End If
End If
Next checkBox
End With
CheckBoxCount = count
End Function
Now, Save the code and go back to Excel File. Insert the following formula in the cell that you want the count of checked boxes.
=CheckBoxCount()
And you will have the count of checked boxes.
Hope this solution helps address your specific requirements in a more efficient manner.
If you have any further queries, kindly post them on our Exceldemy Forum.
Have a nice day!
Regards,
Priti