# Checkbox function to automatically add to total [duplicate]

I'm doing my club's accounting in a Google spreadsheet. I am looking for a way to put a checkbox at the beginning of a line in order to tick off the members who paid their dues and then have a function that will automatically add the amount of the "dues paid" cell to a "total dues paid" cell, elsewhere in the sheet.

Right now I'm doing it with a function of the type `SUM(A1:A600)-A3-A20-A120` etc.—not very elegant…

If anyone could point me to a solution or a resource to learn how to implement this, I would appreciate it!

• I'm not sure why this has been marked a duplicate. It's a completely different question to the one that has been linked to...
– Luke
Oct 2 '19 at 1:17

This is how you do it now (according to Google):

1. Select the cells you want to have checkboxes.
2. In the menu at the top, click Insert and then Checkbox.

Use this in combination with `COUNTIF` (or `SUMIF`, depending on what you want to count). Check for `TRUE`/`FALSE`.

• @markiv What is the purpose of using an `ARRAYFORMULA()` in this case? :) May 16 '18 at 12:55
• You're absolutely right. Updating my comment: `=COUNTIF(A1:A10, "TRUE")` works just as well when I just want to count the number of checked boxes in that range. May 17 '18 at 16:01

To build on the accepted answer, you can tweak that a bit to get checks and x's.

Use two cells in your sheet, one to hold the formula `=char(10003)`, and the other to hold `=char(10005)`.

Now follow the accepted answer exactly, but instead of "List of items" select "List from a range" and choose the two cells you just created.

This will create a dropdown with a check mark and an 'x' mark in it.

• Great idea! You don't have to use `List from a range`, though, if you don't prefer that. You can use the symbols directly in the `Data Validation`-dialog, and the sheet functions. Nov 12 '14 at 16:03

I believe I have a better solution, please see below. I don't normally post on forums, but since I could not find an acceptable solution to this problem I made my own.

This will create an actual check box, bullet, radio button, or really whatever you want.

1. first create create some images to toggle on and off. I used these:

1. Create a script that will toggle a 1 or 0 in the cell adjacent to your check box cell.

2. in the check box cell enter a formula similar to this (this formuls assumes your check box is in cell B2):

=if(A2=1,image(" IMAGE URL FOR CHECK "),image(" IMAGE URL FOR UNCHECK "))

3. Now overlay your check box cell with a blank image.

4. Assign your script to the blank image and you have the illusion of checking and un-checking the box.

Might sound a little complicated but it works very well.

• It's a good idea if you have only a few cells, it gets rather time consuming when the number of rows increase. You have to assign the script to the image on each row, right? One at a time? Note that Gregwah (who asked the question) checked 600 rows. Nov 12 '14 at 16:11
• You are correct. It would be quite time consuming to add this to 600 rows. Hope this can help someone out. Nov 12 '14 at 17:01

I think the problem is that you're trying to use a spreadsheet like a database, or an application. Why not use something like Ragic instead? You can add different types of fields, including selection checkboxes like you want.

Disclaimer: I work at Ragic and have been designing many database applications with it.

• I believe a spreadsheet is a good tool for this, another application is not necessary. Mar 26 '14 at 9:16
• How is access controlled? This would work for me if people could access my spreadsheet/db via a google account or some such. Mar 15 '16 at 17:48
• @crimsonpandaren: this looks pretty cool (but the name is terrible) Nov 16 '17 at 20:59

Here are three easy steps to achieve tick/cross success:

1. In the Google Sheets spreadsheet go to Tools -> Script Editor.
2. Enter the following code:

``````function onEdit() {
}
}
}
``````
3. Enter into any cell a 1 for a tick, and a zero for a cross.

4. Be sure to save it and allow scripting on the spreadsheet.

Alternatively, use a combination of the posts above.

1. Click DataValidation
2. Enter information as needed for your columns
3. Use "List of Items" for your criteria and use the image command to utilise the images of the check box and unchecked box.

I.e.: instead of `yes,no` for your list use `=image("http://i.stack.imgur.com/GChKZ.jpg"), =image("http://i.stack.imgur.com/yQalm.jpg")`

This will allow you to use the drop down menu to select images to insert. This does get flagged as invalid cell contents but it still works.