How flexible can the COUNTIF function be? We have seen a lot of examples from our previous tutorials. In this step-by-step tutorial we show you how to use it to count checkboxes.
It's easy to count checkboxes in google sheets using the COUNTIF function. Check the syntax and the step-by-step example below to see it in action.
=COUNTIF(range,TRUE)
Where
f
range is where they are located
It’s that simple! See why through our example below.
Select the range, then click Insert in the main toolbar, then choose Checkbox. Check our complete tutorial for adding a checkbox here.
Ticking one sets the value of the cell to TRUE while leaving it unticked sets its value to FALSE. This value can easily be accessed by formulas in other cells.
Add the formula to another cell, setting the range. For our example it is stored in C2:C10 so we use the formula =COUNTIF(C2:C10, TRUE). Once you hit enter you will see the number of ticked checkboxes in your range.
Yes! Sometimes you need to tally empty boxes instead of the ticked ones. The solution is simple. The syntax for the formula is:
=COUNTIF(range,FALSE)
Where
range is where the boxes are located
Empty boxes have a FALSE value that can likewise be accessed by formulas you add.
Using the same example as above, we add the formula, setting the range to C2:C10 and the value to FALSE:
Yes! It is possible to set customized values instead of the default TRUE for ticked and FALSE (0) for unticked boxes. For example, maybe you want true to be “closed” and false to be “open”.
Ticked cells
=COUNTIF(range,value_if_true)
Where
range is where the boxes are located
value_if_true is the value you set if it is ticked
Unticked cells
=COUNTIF(range,value_if_false)
Where
range is where the boxes are located
value_if_false is the value you set if it is not ticked or unticked
Step 1: Add the checkbox.
Step 2: Highlight your range and then click Data -> Data validation.
Step 3: Choose Checkbox under criteria and tick the Use custom cell values.
Step 4: Set the values for checked and unchecked. Click Save.
Step 5: Tick the cells
Step 6: Add the formula using the syntax =COUNTIF(range,value_if_true)
Follow the syntax, setting the range and the value depending on what you need to look for.
No. What the formula can indirectly detect, through the set criterion, is whether you ticked or not, but it cannot distinguish whether the cell has a checkbox or not.
If you attempt to count the entire sheet as a range for both ticked and unticked, those that do not contain the right data to be counted will simply be ignored by the function.
If you enjoyed this article, you might also like our article on how to count unique values in Google Sheets or our article on how to count cells with specific text in Google Sheets.
If you want to learn how to send emails based on dates in Google Sheets, we also suggest checking out our detailed guide.
