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.

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

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.