How would I get the checkboxes in column C to add the value from Column B in the same row they’re in to add that value to the total in C7?
For example if the user checks the checkboxes in C3 and C5 the total displays 205 in C7.
If they want Shoes, the sock, and TheOtherSock it displays 106.
If they then decide they only want TheOtherSock (laundry gremlin, they only need one) and uncheck the box in C6 the total in C7 changes to 101.
Your task can be solved very simply using a formula like as =SUMPRODUCT(B2:B6;C2:C6)
But before using it, edit each checkbox. Give each of them a unique name (number). This is not necessary, it will not affect performance, but it will greatly facilitate the task of further editing your spreadsheet (for example, if, as a result of careless formatting, all checkboxes are collected in one cell).
See little black brackets near your checkboxes? This is part of the letter C, the first letter of the checkbox label. It’s not beautiful. Just clear all your checkbox labels, leave them blank.
For each checkbox in the Data tab, fill in the “Linked Cell” field. You ignored this parameter in vain - without displaying the checkbox status in a sheet cell, it is difficult to get its value, you need to write a special macro.
Last step. Now, when you click on the checkboxes, TRUE or FALSE appears in the linked cells. These are the values that will be used in the formula (TRUE = 1 multiplied by the value in column B). It is better to hide these words from the user. The easiest way to do this is to set the font color for the cells in column C to be the same as the background color, white.
As a result, you will get something like this - CheckBox_Addition_Result.ods
YOU ROCK, JohnSun!! Thank you so much!!! LOL@ Hiding the text that way.
There isn’t a way to easily add multiple checkboxes to a sheet with Libre Calc, is there?
Easy way (similar to drag and drop)? No, I don’t know that way. You can write a macro that will create a lot of checkboxes, insert them into specified cells, bind their status to specific cells … I don’t think this method is simple. In addition, the controls embedded in the spreadsheet make the spreadsheet a lot heavier. I recommend reconsidering the approach to the problem: thousands of checkboxes are a lot. How about multiple checkboxes in a dialog form for editing, for example, one row from a table?
This is a similar project as the previous one you helped me with except this one has 60 mission categories. Each mission category has 10 to 25 submissions. Users are paid game currency for each mission they complete. Right, there’s almost 1100 missions. I’ve already begun moving the mission sheets per category to different, separate, sheets so they’re by them selves. Having it all in one spreadsheet file it was already getting heavy with all of the data (etc) in it.
sumif-checked.png
If you don’t want to get into the habit of using ones or zeros (1=True or 0=False) in a field (I always do this myself), maybe it would be easier to use ✓ in a cell + macro instead of linked CHECKBOX control.
And the formula is the same (added to the next column for comparison with the already proposed solution): =SUMPRODUCT(B2:B6;D2:D6=“✓”)
sumif-checked.ods
' Used by: "Double click" sheet event.
Function doSelect(e)
Dim oAddr
oAddr = ThisComponent.NamedRanges.getByName("Check").ReferredCells.RangeAddress
If e.CellAddress.Column = oAddr.StartColumn _
And e.CellAddress.Row >= oAddr.StartRow And e.CellAddress.Row <= oAddr.EndRow Then
e.String = IIf(e.Formula = "✓", "", "✓")
End If
doSelect = True 'cancel Edit Mode
End Function
Thanks! That didn’t work reliably in the 5 cells that do it though. The actual project has to do this thousands of times in thousands of cells. Single-click and a box that screams “click here!” is so much easier. That wasn’t any faster or easier. Maybe adding the checkboxes is easier (if I had a clue how in the world you even did that)? But the clicking action is slower and doesn’t work as reliably as it needs to function. Maybe Libre isn’t the right suite for this project.
No, everything works reliably for me.
Another question: what operating system do you use?
Windows 10 Home 64bit.
The previous answer works reliably for me so that’s what I’ve decided to use. A very similar macro was suggested in another similar question I asked. Neither worked and macros baffle me. I don’t know a thing about OO or Libre or Excel macro scripting, how to assign them, how to make them work, etc.
So far, except for one project, I’ve been able to do everything I wanted with formulas thanks to help I receive here. Great site! Great people here! I appreciate your time and suggestion very much! Thank you!
@Bort I use Linux, but if Windows then take a look at Marlett font. The a (b also; see link) character on the keyboard corresponds exactly to the ✓ (checkmark). Well, just in case you run into performance issues. Accordingly, a should be entered as a criterion in the SUMPRODUCT function, although a checkmark will be displayed. You can erase it with the Del key.
https://reactos.org/wiki/Marlett_Characters
=SUMPRODUCT(B2:B6;D2:D6=“a”)
