Way back in 2018, Kim introduced you to a new Google Sheets feature called checkboxes. And then in 2019, Julianne explained how to use the COUNTIF function to… well… count things. In today’s Tech Tip, I’m going to show you how to combine these two features for a super-handy function that will help with tracking data.
In case you missed Kim and Juli’s tips, they should be linked above.
Here’s a quick review on how to add checkboxes to your Google Sheet:
Also remember that unless you change them through data validation, a checked box has a value of TRUE, while an unchecked box has a value of FALSE.
Now, here’s a quick review on how to use COUNTIF in your Google Sheet:
In the image above, we are counting the number of times that the word taxi appears in the range A2:A9. Remember that the actual syntax of COUNTIF is:
COUNTIF(range, criterion)
range– The range that is tested againstcriterion.criterion– The pattern or test to apply torange.
Also remember that your criterion can be text (must be in quotation marks), a number, another function, or an operation (<10 for example).
Now that we have that down, let’s combine these two concepts.
Say I am working with a group of 13 students on a behavior plan where if they have accomplished their behavior goal for the day, they receive a check, and if they did not, they don’t. Here’s the sheet with the checkboxes in it:
Suppose I want to get a total for each child for the week (the number of times that they met their goal). I can use the COUNTIF function, with the range of B2:F2 and the criterion of the box being checked, or TRUE. Remember that you always start with an = and then put the range and criterion in parentheses.
I’ve completed the function and hit the ENTER key. Note that Sheets asks me if I want to Autofill down. I sure do! So I click the check button to do so.
Now I’ve got my totals. If I uncheck or check the boxes, the total will change to reflect that.
What if I want to get a total of the checked boxes for a particular day? I can do that too. I just need to reference the rows instead of the columns, like this:
Once I fill across, I now have the totals for each student and for each day. I can begin to make some inferences with my data. Looking in the total column, I can quickly see that a couple of my students have met their goals every day, but I have one that only met it one time.
Looking in row 15, I can see that the students have the most trouble meeting goals on Mondays and Fridays. Why is that so, when on Wednesday, nearly every student made goal? What is different about Monday and Friday? This allows me to make decisions driven by the data collected.
Combining checkboxes with COUNTIF is a great way to start making your data work for you. One caveat though… the range must always be contiguous. I couldn’t count only Mondays and Fridays in this example.
To do that, I would need to use a =SUM function and then combine that with two or more COUNTIF statements. That’s a little more advanced.
So, I encourage you to try using checkboxes and COUNTIF to manipulate and analyze your data. Play around and see what cool things you can make. If you get stuck, don’t forget that help is built into every Google Sheet (just look for the blue ?). If you want a complete list of functions and how to use them, check this out.