How to Count Checkboxes in Excel (3 Easy Methods)

Get FREE Advanced Excel Exercises with Solutions!

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.

How to Count Checkboxes in Excel

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.

How to Count Checkboxes in Excel

  • 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.

How to Count Checkboxes in Excel


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.

How to Count Checkboxes in Excel

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.

How to Count Checkboxes in Excel

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.

How to Count Checkboxes in Excel


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.
=COUNTIF(E5:E9,TRUE)

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.

How to Count Checkboxes in Excel


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.

How to Count Checkboxes in Excel

  • 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.

How to Count Checkboxes in Excel

  • 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.

How to Count Checkboxes in Excel

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.
=COUNTIF(E5:E9,TRUE)

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.

How to Count Checkboxes in Excel

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.

How to Count Checkboxes in Excel

  • 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.

How to Count Checkboxes in Excel

  • Then, from the Developer tab >> you have to select Macros.

  • Now, you need to select the Macro named CountCheckbox and click on Run.

How to Count Checkboxes in Excel

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.
=COUNTIF(E5:E9,TRUE)

How to Count Checkboxes in Excel

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.

How to Count Checkboxes in Excel


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


<< Go Back to Excel CheckBox | Form Control in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Musiha Mahfuza Mukta
Musiha Mahfuza Mukta

Musiha Mahfuza Mukta is a marine engineer who loves exploring Excel and VBA programming. To her, programming is a time-saving tool for dealing with data, files, and the internet. She's skilled in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. With a B.Sc in Naval Architecture & Marine Engineering from BUET, she's shifted gears and now works as a content developer. In this role, she creates techy content exclusively focused on Excel... Read Full Bio

2 Comments
  1. 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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo