Assign Values to Tick Boxes and Calculate the Total in Google Sheets

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:

  1. Through the Insert Menu: Click Insert > Tick box.
  2. 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:

  1. Assume you have tick boxes in the range B2:E5.
  2. Select the range B2:E5.
  3. Click Data > Data Validation.
  4. In the data validation sidebar panel, select the tick box that you want to edit and check Use custom cell values.
  5. Enter a value for “Ticked” (e.g., 5) and for “Unchecked” (e.g., 0).
  6. Click Done.
Assigning Custom Values to Existing Tick Boxes in Google Sheets

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:

SupplyInstallationTestingCommissioning
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))
Calculating Total of Custom Values Assigned to Tick Boxes in Google Sheets

Resources

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Expert Prashanth KV is a Diamond Product Expert in Google Sheets, officially recognized by Google for his contributions to the Docs Editors Help Community and featured in the Google Product Experts Directory. Explore his blog to learn advanced formulas, automation tips, and problem-solving techniques to elevate your spreadsheet skills.

Mode of Comma-Separated Numbers in Excel (Dynamic Array)

There is no dedicated function in Excel to directly find the mode of comma-separated...

How to Find Mode of Comma-Separated Numbers in Google Sheets

Working with comma-separated numbers inside a single cell is a common scenario in Google...

How to Count Merged Cells in Google Sheets (and Get Their Size)

Sometimes, you may have blocks of merged cells in a column or row. But...

Count Consecutive Workday Absences in Google Sheets

This tutorial offers a powerful formula-based solution to count consecutive workday absences in Google...

More like this

How to Find Mode of Comma-Separated Numbers in Google Sheets

Working with comma-separated numbers inside a single cell is a common scenario in Google...

How to Count Merged Cells in Google Sheets (and Get Their Size)

Sometimes, you may have blocks of merged cells in a column or row. But...

Count Consecutive Workday Absences in Google Sheets

This tutorial offers a powerful formula-based solution to count consecutive workday absences in Google...

2 COMMENTS

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.