How to Count Checkboxes in Excel (3 Easy Methods)

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.


Download Practice Workbook

You can download the practice workbook from here:


3 Methods to Count Checkboxes in Excel

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 insert Checkboxes in Excel to Count those. 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

Read More: Excel VBA: Form Control Checkbox Value (3 Examples)


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 for going 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

Read More: How to Filter Checkboxes in Excel (with Easy Steps)


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 (4 Methods)


3. Use of VBA Code to Count Checkboxes in Excel

You can employ the VBA code 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 Macro (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 (3 Ways)


💬 Things to Remember

  • 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


Conclusion

I hope you found this article helpful. Here, I have explained 3 methods of How to Count Checkboxes in Excel. You can visit our website Exceldemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles

Musiha

Musiha

I am Musiha, graduated from Naval Architecture and Marine Engineering Dept., BUET, currently residing in Dhaka, Bangladesh. And my passion is to grow up my skillsets with industry demands.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo