Hi everyone. In this article, we will learn how to count checkboxes in Google Sheets. Checkboxes, as you know are very significant in today’s date. We use checkboxes to validate input from users in the form of checked and unchecked which logically means yes or no. We use them to take Boolean inputs such as yes or no, true or false, and so on.
We often need to count them like normal data. We have COUNT, COUNTA like functions to count normal data in Google Sheets, but checkboxes are not normal. They are logical and they may have checked or unchecked checkboxes, so counting them using a normal approach will probably not work ideally.
So, how to count checkboxes in Google Sheets? Well, it’s very easy and straightforward. Today, we are going to learn how to count checkboxes in Google Sheets using the count function and some variants of the count function. We will count checked and unchecked boxes separately so that we have a clear demonstration of our data.
Use case: Count Checkboxes in Google Sheets
Just like normal data, we also need to count the checkboxes in Google Sheets. When we have an extensive data set with checkboxes that take yes or no input from the users in an extensive list, it can be a checklist, a shopping list, or a to-do list. So, what we need, we need to see the user input for each item of the checkbox, so here we can simply count them. But, how? Simply, we will use COUNTIF to count all the checkboxes that are checked, and then all the unchecked checkboxes.
We can also build custom values for the checkboxes instead of True and False. We can set values like Yes and No, Accept or Reject, and so many combinations of it.
We will get a number such as 23 for the unchecked checkboxes, and another number such as 17 for the checked checkboxes, so there is how to count checkboxes in Google Sheets and its purpose. Therefore, we need to learn how to count checkboxes in Google Sheets.
How to Count Checkboxes in Google Sheets?
From this section, we will learn how to make checkboxes, then how to count checkboxes in Google Sheets are both checked and unchecked separately. We will also learn how to add custom logic to checkboxes in Google Sheets and then count customized checkboxes. So, without further delay, let’s get started.
How to Count Checkboxes in Google Sheets – Insert Tickbox
Let’s see how to create checkboxes in Google Sheets. It’s pretty easy to make checkboxes and use them as checked or unchecked checkboxes.
Open a Google Sheets file, or launch a new Sheet with a blank template
Select a cell, then go to Insert > Tickbox
A tickbox is added to your selected cell, and now you check or uncheck it.
Here, we have another method to add a checkbox with custom validation. Let’s see how to do that.
How to Count Checkboxes in Google Sheets – Data Validation
In this section, we will learn how to count checkboxes in Google Sheets. We will see a method to add a checkbox with custom validation. Let’s go to the steps and see how it works.
Select a cell or multiple cells in which you want to add checkboxes.
Go to Data > Data Validation
A new pop-up will appear, now in the criteria section, select the tickbox from the list
Below you can see a checkbox named “use custom cell values”, check it to use custom values for True and False.
You will get two input boxes, now here you can add two values one for the ticked, and the second for the unticked checkbox.
Provide values, and click on the Save button
Your checkboxes are added to the select cell(s).
Note: You may think, why do we need to use custom values when they are not visible at all? Yes, you’re right, custom values do not appear, but it is used logically when you apply a formula or function to them. We will see it in the below sections.
How to Count Checkboxes in Google Sheets – Using COUNTIF
Finally, in this section, we will learn how to count checkboxes in Google Sheets. We have already learned two methods to insert checkboxes in Google Sheets, now we will how to count them separately. Let’s see the below steps.
Select a cell where you want to get the count of the checkbox.
Add the COUNTIF Function
Pass the data range having all the checkboxes as the first argument
Pass the argument value you want to count, such as True or False (True if you want to count checked checkboxes, and False if you want to count the unchecked checkboxes)
Hit Enter, and you’re done. Now, try checking and unchecking different checkboxes, and you will see the resultant value will be changed.
This is how to count checkboxes in Google Sheets with default cell values.
How to Count Checkboxes in Google Sheets – Using COUNTIF for Custom Values
So, when we add use custom fields then we have to change the function as well. The input fields do not appear on the sheets, but they are used in the background for logic building. Also, note that we cannot use more than two custom values because understandably a checkbox can have only two states, one is checked, and the other is unchecked, so we have only two corresponding values for True and False.
Add COUNTIF function
Pass the range as the first argument
Pass the name of the custom values in double quotes, for the value you want to count such as “Yes”.
Add another COUNTIF function to count the other inputs such as “No”
This is how you have to change the String value in the second argument to count the custom values for the checkboxes.
How to Count Checkboxes in Google Sheets – Conditionals
In this section, we will learn how to count checkboxes in Google Sheets using conditional. Yes, using COUNTIF is also a condition, but this is a function-based condition, what if we want to count checkboxes based on custom conditions as per our data requirements? Yes, it’s possible, so in this section, we will learn how to count checkboxes in Google Sheets based on outer conditions and we will use COUNTIFS instead of COUNTIF, you will see how this method helps us. Let’s see the steps.
Write the formula COUNTIFS (plural of COUNTIF)
Now pass two arguments just like you did in the previous section
In the second iteration, you need to pass another set of arguments for the second IF. So here, we will pass the sections that have tasks inside, and then the first address of the resultant cell.
So here, you can see we have got the distributed values for the count checkboxes because we used TRUE.
We can similarly use FALSE instead of TRUE to count them based on unchecked checkboxes.
So, this is how to count checkboxes in Google Sheets using different methods and using different scenarios. I hope you find the above article helpful.
Get a Free Template used in this Article
- The custom values cannot be exceeded by two, because the checkbox has only two possible states
- The cell values must be used inside String, double or single quotes if they are custom, although default True and False values can also be used without double or single quotes.
- You can set a wrong input warning when a user attempts a wrong input, but it’s only possible when a user is using a function, it is not possible directly. You can either show a warning or reject input on the invalid input.
Frequently Asked Questions
How to Count all checkboxes in Google Sheets?
There is no direct method to count all the checkboxes together, but there is an easy workaround to do this. Sometimes, we need to count all the checkboxes together to verify if the required checkboxes are added or not. So, what we can do, we can simply apply a COUNTIF function to count checked checkboxes, and then another COUNTIF function to count unchecked checkboxes, then we will sum both of the resultant cells. This gives us a total count of all the checkboxes in the selected cell range
How to Count checked tickboxes in Google Sheets?
We can count checked tickboxes in Google Sheets by using a COUNTIF function with the first argument as the cell range (having tickboxes), and the second argument as a “True” Boolean value. Note: False value will give you to count for all unchecked checkboxes.
Can we add custom input values to checkboxes in Google Sheets?
Yes, we can add custom input values to checkboxes in Google Sheets using data validation features. We can pass any String value corresponding to the checked state, and another different String value corresponding to the unchecked state. These values must be used as a String inside double or single quotes when you are using them inside a function or custom formula.
What is the difference between Tickbox, and Checkbox?
There is no difference between Tickbox and Checkbox, both are identical, and they are only two names of the same thing.
This was all about how to count checkboxes in Google Sheets. We learned various methods and workaround to count checkboxes, we learned what’s the logic behind them, and how to count unchecked and checked checkboxes separately. I also tried to solve some common problems in the FAQs section, so having that said. I am ending this tutorial here. I hope you find it useful and helpful. I will see you soon with another helpful guide. Keep learning with Office Demy.