Checkbox function to automatically add to total [duplicate]

Question

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!

Answer 1

Google has now (2018-05) added checkboxes!

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.

Answer 2

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.

Answer 3

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:

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

3. 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 "))

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

5. 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.

Answer 4

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.

Answer 5

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() {
     if(SpreadsheetApp.getActiveRange().getValue() == "1") {
       SpreadsheetApp.getActiveRange().setValue('=CHAR(10004)');
       SpreadsheetApp.getActiveRange().setBackgroundRGB(0,255, 0);
     }
     if(SpreadsheetApp.getActiveRange().getValue() == "0") {
       SpreadsheetApp.getActiveRange().setValue('=CHAR(10060)');
       SpreadsheetApp.getActiveRange().setBackgroundRGB(255, 0, 0);
     }
   }
   

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.

Answer 6

Alternatively, use a combination of the posts above.

1. Click Data → Validation
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("https://i.stack.imgur.com/GChKZ.jpg"), =image("https://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.