In this tutorial, you will learn how to assign values to tick boxes and calculate their total in Google Sheets. I will walk you through some practical examples that demonstrate how to use tick boxes with custom values.
Earlier, I posted a tutorial that discusses various ways to insert tick boxes in Google Sheets. I recently updated that post with a new tick box feature, which you might find interesting: Insert CheckBox (Tick Box) in Google Sheets.
Now, let’s focus on assigning values to tick boxes and calculating the total. Tick boxes in Google Sheets have become increasingly popular due to their versatility.
Inserting Tick Boxes
You can insert tick boxes in Google Sheets in two ways:
- Through the Insert Menu: Click Insert > Tick box.
- Through Data Validation: Click Data > Data Validation > Add Rule. Under Criteria, select Tick box.
Assigning Values to Tick Boxes
You can assign values other than TRUE or FALSE to tick boxes in Google Sheets. These values can be numeric, text, dates, or special characters. You can also allocate different or identical values to each tick box.
Assign Values to Existing Tick Boxes
To assign values to existing tick boxes, follow these steps:
- Assume you have tick boxes in the range B2:E5.
- Select the range B2:E5.
- Click Data > Data Validation.
- In the data validation sidebar panel, select the tick box that you want to edit and check Use custom cell values.
- Enter a value for “Ticked” (e.g., 5) and for “Unchecked” (e.g., 0).
- Click Done.
Testing the Setup:
Check the tick boxes in cells B2, C2, and E2. The following formula will return the total of the values for checked tick boxes:
=SUM(B2:E2)
This formula should return 15 if three tick boxes are checked and assigned the value of 5.
Assign Values to New Tick Boxes
To allocate custom values to new tick boxes, use the Data > Data Validation method instead of the Insert > Tick box method. This way, you can assign values while inserting the tick boxes.
Real-Life Example
I have a table with the following format in cells A1:E2:
Supply | Installation | Testing | Commissioning | |
33kV Transformer. Installation, Testing & Commissioning | ᷋ | ᷋ | ᷋ | ᷋ |
I’ve created tick boxes in B2, C2, D2, and E2 with the custom values 25%, 35%, 30%, and 10%, respectively.
When each job is completed, I’ll check the corresponding tick box. For example, if the supply and installation of the item are completed, I will tick the boxes in B2 and C2.
The following formula will return the completion percentage accordingly:
=TO_PERCENT(SUM(B2:E2))
Resources
- Convert TRUE or FALSE to Checkboxes in Google Sheets
- Tick Mark: Lock and Unlock Cells Using Checkboxes in Google Sheets
- Vlookup in Checkbox Checked Rows in Google Sheets
- Change the Tick Box Color While Toggling in Google Sheets
- 10 Best Tick Box Tips and Tricks in Google Sheets
- Check-Uncheck a Tick Box based on the Value of Another Cell in Google Sheets
- Highlighting Multiple Groups and Control Tick Boxes in Google Sheets
- Create a List from Multiple Column Checked Tick Boxes in Google Sheets
Thank you!!! This helped so much with my merch pre-orders!
Thank you so much, this helped a lot! Keep up this awesome work.