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 Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Cycle Highlights in Google Sheets – Rotate Highlights Daily

Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

Filter Rows Containing Multiple Selected Values in Google Sheets

This tutorial explains how to filter rows in a column containing multiple selected drop-down...

Two-Way Lookup with XLOOKUP in Google Sheets

When you need to look up one search key vertically and another horizontally, you...

How to Filter by Total in Google Sheets Pivot Tables

Google Sheets offers many tools to summarize and analyze data, but Pivot Tables are...

More like this

Cycle Highlights in Google Sheets – Rotate Highlights Daily

Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

Filter Rows Containing Multiple Selected Values in Google Sheets

This tutorial explains how to filter rows in a column containing multiple selected drop-down...

Two-Way Lookup with XLOOKUP in Google Sheets

When you need to look up one search key vertically and another horizontally, you...

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.