Sometimes you may need to use Checkboxes in Excel. In that case, you may need to count the Checkboxes in Excel. In this article, I will explain how to count Checkboxes in Excel.
How to Count Checkboxes in Excel: 3 Methods
In this article, I will explain 3 methods to count Checkboxes in Excel. For more understanding, I’m going to use the following data set. Which contains 3 columns. They are Name, ID , and Attendance.
1. Use of Properties and COUNTIF Function to Count Checkboxes in Excel
By using the COUNTIF function I will count the Checkboxes. To do so, first I will insert checkboxes then I will Link the cell to the checkboxes.
1.1. Inserting Checkboxes
Here, I will add checkboxes in Excel and then count them. Here, I will describe how to make Checkboxes. Furthermore, for your better understanding, I’m going to use the following sample dataset. Which has two columns named Name and ID.
Steps:
- Firstly, you need to select a cell where you want to include a Checkbox. Here, I have selected cell D5.
- Secondly, you have to go to Developer Ribbon.
- Thirdly, from the Insert tab >> you must choose Checkbox under Form Controls.
- At this time, you have to drag the Mouse Pointer.
- Now, you can see the Checkbox.
If you want, you can edit the text adjacent to the Checkbox. Here, I have simply removed the text.
1.2. Using Properties Feature
Now, you can use the Properties feature to link the cell and later will count the Checkboxes. Here, firstly, you have to link the cell. Let’s start with the linking of the cell.
Steps:
- Firstly, you have to Right-Click on the Checkbox for selection.
- Secondly, from the Developer tab >> you may go to Properties.
At this time, a dialog box named Format Control will appear.
- Now, from the dialog box you need to go to the Control Menu.
- Then, you may click on the Selection Arrow of Cell link.
- Now, you have to choose the cell where you want to keep the status of the Checkbox. Here, I have selected the E5 cell.
- Then, you need to click on the Selection Arrow to go back to the whole dialog box.
- Subsequently, you must click on OK to get the result.
Here, you will see the status of that cell. Basically, if the Checkbox is checked then it will show the status as TRUE. Furthermore, in case of unchecked Checkboxes, it will show the status as Blank. In addition, if you unchecked the Checkbox after linking up the cell, then it will show the status as FALSE.
Similarly, you have to link all the Checkboxes individually to cells. After doing this, you will see the following output.
1.3. Use of COUNTIF Function
Now, for the counting of the Checkboxes, I will use the COUNTIF function. Here, you have to follow the corresponding steps.
- Firstly, you have to select a cell, where you want to keep the result. I have selected a merged cell from F5:F9.
- Secondly, you need to 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 I want to count. TRUE is the criteria. So, in the E5:E9 range if there is any TRUE then it will count that.
Finally, you can see the total number of checked Checkboxes.
2. Using Context Menu Bar and COUNTIF Function
You can use the Context Menu Bar to link the Checkboxes and can use the COUNTIF function to count the Checkboxes in Excel. The steps are given below.
Steps:
- Firstly, you have to Right-click on the Checkbox.
- Secondly, from the Context Menu Bar, you need to choose Format Control.
At this time, a dialog box named Format Control will appear.
- Now, from the dialog box you need to go to the Control Menu.
- Then, you may click on the Selection Arrow of Cell link.
- Now, you have to choose the cell where you want to keep the status of the Checkbox. Here, I have selected the E5 cell.
- Then, you need to click on the Selection Arrow to go back to the whole dialog box.
- Subsequently, you must click on OK to get the result.
Here, you will see the status of that cell.
Similarly, you have to link all the Checkboxes individually. After doing that, you will see the status for all.
Now, for the counting of the Checkboxes, you have to follow the corresponding steps.
- Firstly, you have to select a cell, where you want to keep the result. I have selected a merged cell from F5:F9.
- Secondly, you need to 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 I want to count. TRUE is the criteria. So, in the E5:E9 range if there is any TRUE then it will count that.
Finally, you can see the total number of checked Checkboxes.
Read More: If Checkbox Is Checked Then Apply Formula in Excel
3. Use of VBA Code to Count Checkboxes in Excel
You can employ VBA to count the Checkboxes in Excel. The steps are given below.
Steps:
- Firstly, you have to choose the Developer tab >> then select Visual Basic.
- Now, from the Insert tab >> you have to select Module.
- Write down 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
- Here, we have created a Sub Procedure named CountCheckbox.
- Next, we have declared a variable Chkbx as CheckBox to call the CheckBox.
- After that, we used a For Each Loop to link all the Checkboxes to cells which will return the Status.
- Now, Save the code then go back to Excel File.
- Then, from the Developer tab >> you have to select Macros.
- Now, you need to select the Macro named CountCheckbox and click on Run.
At this time, you can see the Status result.
Now, for the counting of the Checkboxes, you have to follow the corresponding steps.
- Firstly, you have to select a cell, where you want to keep the result. I have selected a merged cell from F5:F9.
- Secondly, you need to 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 I want to count. TRUE is the criteria. So, in the E5:E9 range if there is any TRUE then it will count that.
Finally, 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. Here, the basic difference between those methods is about linking the Checkboxes to the cells.
- Moreover, I will prefer the 3rd method for the linking process. Because, in this case, you don’t need to do it individually. You can do it all at once.
Practice Section
Now, you can practice the explained method by yourself.
Download Practice Workbook
You can download the practice workbook from here:
Conclusion
I hope you found this article helpful. Here, I have explained 3 methods of How to Count Checkboxes in Excel. Please, drop comments, suggestions, or queries if you have any in the comment section below.
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