In this Spreadsheet tutorial, that about Tick Boxes, you can learn how to assign values to Tick Box and Total it in Google Sheets. In the coming paras, I am taking you through some basic but real life examples to the use of Tick Box with custom values in Google Doc Spreadsheet.

Earlier I’ve posted a tutorial that provides different options to insert Tick Box in Google Sheets. Recently I’ve updated that post with new tick box feature. You may find that post interesting – Insert CheckBox, Tick Mark in Google Sheets.

Now back to assign values to Tick Box and Total it in Google Sheets. Tick Box in Google Sheets is the talk of the town nowadays. I am also quite satisfied with the way we can use Tick Box in Google Sheets.

There are two different ways one can insert Tick Box in Google Sheets. It’s (1) directly through the insert menu and (2) through the Data menu Data validation setting.

Tick Box Through the Insert Menu:

Tick Box Through Data Validation:

Assign values to tick box other than TRUE or FALSE is possible in Google Sheets. You can have any numeric, text, date, special characters assigned to Tick Box. Also, you can have different values or identical values for each Tick Boxes.

Assign Values to Tick Box and Total It in Google Sheets

Tick Box Using Insert Menu:

When you insert Tick Box via the Insert menu, it inserts Tick box instantly in the active cell. By default the value of the Tick Box (the cell where the Tick Box is) is FALSE. When ticked, it becomes TRUE.

Here users have no freedom to assign custom values to Tick Boxes (I mean the Insert menu Tick Box). But there are workarounds.

The below examples will give you some insight into the use of custom values in Tick Boxes.

Example 1:

I am just inserting a Tick Box in Cell A1 and assigning a value to it in another Cell.

In this example the value of Tick Box in cell A1 is TRUE when clicked, else it’s FALSE. But with logical IF, I’ve assigned the value 10 to it when clicked and 0 if not. Please refer the formula in Cell B1 above.

You can have the value 10 itself in Cell A1. I’ll come to that later in Example 3 and 4.

Example 2:

The above logic, we can use in a real-life example below. This example justifies the title of this Spreadsheet tutorial; assign values to Tick Box and total it in Google Sheets. How?

In this example, one company has been awarded a job to install a 33kV Transformer. In Cell A2 you can see their job description.

Their job responsibility includes (break up of the job) Supply of Transformer, then Installation, Testing, and Commissioning activities.

When they supply the Transformer, they have completed 25% of their awarded job. Likewise, each activity adds 25% to the total job completion.

Supply (25%) + Installation (25%) + Testing (25%) + Commissioning (25%) = Total 100%

The last activity is the Commissioning and when they commission the Transformer, it reaches 100% that means the job is finished.

There are four columns in this example for each activity. When complete the activity just tick the interactive tick box in the relevant column and that assigns the value TRUE.

Then in Column F2, I can sum the cells B2: E2 as below.

=if(B2=TRUE,25%)+if(C2=TRUE,25%)+if(D2=TRUE,25%)+if(E2=TRUE,25%)

Then drag this formula down in the range F3: F6. Here I’ve assigned the value 25% to each tick mark using formula.

The same formula can be converted to an array formula in Cell F2, which expands automatically.

=ArrayFormula(if(B2:B6=TRUE,25%)+if(C2:C6=TRUE,25%)+if(D2:D6=TRUE,25%)+if(E2:E6=TRUE,25%))

But this types of the formula is OK with limited columns. Also, it breaks when you insert or delete new columns.

In the above-shared screenshot, you can see the formula I’ve used. That’s a flexible formula using MMULT. So you can use that instead of the just above logical array formula.

=ArrayFormula(mmult((if(B2:E6=TRUE,B2:E6*25%,0)),transpose(column(B2:E2)^0)))

Note: Some of you may not be familiar with MMULT. If you want to know how to add the values of multiple columns in Array, see my earlier MMULT tutorial.

Must Read: Array Formula to Sum Multiple Columns in Google Sheets

Now I am moving to the second part of this tutorial that related to Tick Box in Data Menu.

If you insert Tick Box through Data Validation, there is no such hassle. I mean you can assign values to Tick Box and total it in Google Sheets without depending complex formula.

You can simply use a SUM formula to sum the cells. Because we are adding value to the cell that containing the Tick Box, not in the formula. See that example below.

Tick Box Using Data Menu, Data Validation:

Example 3:

Similar to my example 1 above, I am just inserting a Tick Box in Cell A1, but this time via Data Validation.

Here there is one more difference. I am assigning 25% as the value to this Tick Box. That means, when Ticked the value of cell A1 will be 0.25 else 0. Not the TRUE or FALSE!

After inserting the Tick Box in Cell A1 as above, try the below formula in any cell which would return the value 0.25. You can of course format it to 25% later using the Google Sheets Toolbar.

=A1

I am using this method now with the above real-life example 2. See that below.

Example 4:

Note: I forgot to say one thing. No need to go to each and every single cells to insert Tick Boxes.

You can either insert Tick Box in one Cell then copy and paste it to other cells or first you can select the data range, here B2: E6 and then go to the Data Validation and insert multiple Tick Boxes at a time.

Here all the ticked cells in the range B2: E6 has the value of 0.25 (please refer the screenshot in example 3 to know how to get this value). So in Cell F2 you can just use the SUM function as below and copy it to down.

=sum(B2:E2)

Hope you could learn how to assign values to Tick Box and total it in Google Sheets.

Conclusion

In the above example that using Data Validation Tick Box, I’ve used identical values (0.25) in each and every cells in the range. But if you want, you can have different values in each cell. I think no need to explain it. It’s all about what value you put in the data validation settings.

Don’t forget to use interactive Tick Boxes in Google Sheets and make your Spreadsheet more appealing. Enjoy!