If youve ever used a Google Form, you know that you can use checkboxes to allow respondents to select multiple responses for a given question. But what if you want to analyze those responses in a spreadsheet?
In this article, well show you how to sum checkboxes in Google Sheets so that you can quickly and easily see how many people selected each option. Well also show you how to use this technique to calculate percentages.
How to Sum Checkboxes in Google Sheets
To sum checkboxes in Google Sheets, youll need to use the COUNTIF function. This function allows you to count the number of cells that meet a certain criteria. In this case, we want to count the number of cells that contain a checked checkbox.
1. Create a Checkbox
First, lets create a checkbox. You can do this by inserting a tick symbol or emoji into a cell. To insert a tick symbol:
- Click the cell where you want to insert the checkbox.
- Click Insert > Special characters.
- Type “tick” in the search field, then click the tick symbol.
2. Create a Criteria Range
Next, well create a criteria range. This is the range of cells that you want to count. In this case, its the range of cells that contain checkboxes. To do this:
- Select the cells that you want to include in the criteria range.
- Click Data > Named ranges.
- Enter a name for the range, then click OK.
3. Use the COUNTIF Function
Now were ready to use the COUNTIF function. This function takes two arguments: the range of cells to count and the criteria to count. In this case, the range is the criteria range we just created and the criteria is the checked checkbox.
To use the COUNTIF function:
- Click the cell where you want to see the sum of checked checkboxes.
- Type =COUNTIF(
- Select the range of cells that you want to count, then type a comma (,)
- Type the criterion that you want to count, then type a closing parenthesis ().
For example, if the range of cells is A1:A10 and the criterion is the checked checkbox, the function would look like this: =COUNTIF(A1:A10, “”)
How to Use this Technique to Calculate Percentages
You can also use this technique to calculate percentages. For example, lets say you have a list of survey responses and you want to know what percentage of people selected each option. To do this:
- Create a checkbox in each cell of the column next to the option.
- Create a criteria range that includes all of the cells with checkboxes.
- Use the COUNTIF function to sum the number of checked checkboxes for each option.
- Use the COUNT function to count the total number of responses.
- Divide the sum of checked checkboxes by the total number of responses.
1. Create Checkboxes
First, lets create a checkbox in each cell of the column next to the option. To do this:
- Click the cell next to the first option.
- Click Insert > Special characters.
- Type “tick” in the search field, then click the tick symbol.
- Repeat this process for each option.
2. Create a Criteria Range
Next, well create a criteria range. This is the range of cells that you want to count. In this case, its the range of cells that contain checkboxes. To do this:
- Select the cells that you want to include in the criteria range.
- Click Data > Named ranges.
- Enter a name for the range, then click OK.
3. Use the COUNTIF Function
Now were ready to use the COUNTIF function. This function takes two arguments: the range of cells to count and the criteria to count. In this case, the range is the criteria range we just created and the criteria is the checked checkbox.
To use the COUNTIF function:
- Click the cell where you want to see the sum of checked checkboxes.
- Type =COUNTIF(
- Select the range of cells that you want to count, then type a comma (,)
- Type the criterion that you want to count, then type a closing parenthesis ().
For example, if the range of cells is A1:A10 and the criterion is the checked checkbox, the function would look like this: =COUNTIF(A1:A10, “”)
4. Use the COUNT Function
Now well use the COUNT function to count the total number of responses. This function takes one argument: the range of cells to count. In this case, its the range of cells that contain checkboxes.
To use the COUNT function:
- Click the cell where you want to see the total number of responses.
- Type =COUNT(
- Select the range of cells that you want to count, then type a closing parenthesis ().
For example, if the range of cells is A1:A10, the function would look like this: =COUNT(A1:A10)
5. Calculate the Percentage
Finally, well calculate the percentage of people who selected each option. To do this, well divide the sum of checked checkboxes by the total number of responses. For example, if the sum of checked checkboxes is 10 and the total number of responses is 100, the percentage would be 10%.
To calculate the percentage:
- Click the cell where you want to see the percentage.
- Type =
- Select the cell that contains the sum of checked checkboxes, then type /
- Select the cell that contains the total number of responses, then type a closing parenthesis ().
Conclusion
In this article, weve shown you how to sum checkboxes in Google Sheets so that you can quickly and easily see how many people selected each option. Weve also shown you how to use this technique to calculate percentages.
