I'm trying to make a simple calculator in Google Sheets with checkboxes for certain values. For simplicity I'll refer to these Checkboxes as A and B. What I would like to have is the user selects A, B, either, or both and have that output value. However, I want the user to be able to unselect these boxes, but the total from the first value is unaffected.

Right now I have A=5 and B=10. I can check both, and get 15 in a separate cell. I can then subtract that cell from the total. However, when I uncheck the boxes, the values, including the total reset. Is there a function I can use that will total only when commanded, and the result is independent of if the boxes are checked or not?


demo spreadsheet


  • select or unselect checkboxes
  • select desired mathematical symbol
  • click on the equal symbol to get a result

NOTE: in order this to work as it should, you need to make a copy of this sheet and allow the script

  • script is:
    function moveValuesOnly() { var ss = SpreadsheetApp.getActiveSpreadsheet();
                                var source = ss.getRange('F1');
                                source.copyTo(ss.getRange('F2'), {contentsOnly: true}); }
  • formula in F1 is:
    =IF(C2="+", B2+D2,
     IF(C2="-", B2-D2,
     IF(C2="*", B2*D2,
     IF(C2="/", B2/D2, ))))
  • values of checkboxes (B2 and D2) are shadow-mirrored respectively in B3 and D3
  • cell C2 has data validation - eg. choice to select one of math symbols " + ", " - ", " * " or " / "
  • range A1:D1 is data validation range list for C2 consisting of: " + ", " - ", " * " or " / "
  • over the area of the cell E2 there is transparent object (drawing / button) which is activated via single click upon which this object will execute the above script which will copy the value of cell F1 and paste it in F2 cell. transparent object is available by right clicking:


| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.