I am trying to write an if or vlookup statement so when i input a value in c2 through c49 and the column j2 through j49 is check it will add the totals to one column in r2.
I am having trouble creating the forumla though.
see attachment.
@Admin Clayton, despite the checkbox showing in J2, a check box is not automatically associated with the cell that it is underneath it.
In a scenario like the screenshot above, I would strongly advise against using check boxes, precisely because of that. Check boxes are graphical objects that float above the Excel grid, much like placing a transparent film over a piece of paper. When you write with a marker on the transparent film, you don't place any marks on the actual paper sheet.
Assuming that these check boxes are Form controls (as opposed to Active X controls), you need to bind each control to a worksheet cell. Setting up each check box with an associated cell will be a lot of work in a spreadsheet like the one in the screenshot. You need to right-click and edit the properties of each check box. On the Control tab you need to assign the check box a worksheet cell where the checkbox state will be saved.
Now when you tick a check box, the corresponding cell will show TRUE and you can use a Sumif or Sumifs formula to create a sum for only the checked items.
Check boxes were not designed as interaction tools for worksheets. If you use them like that, you need to be aware of these shortcomings and workarounds.
If you want the appearance of a tick or a cross, you could use the font Marlett, which shows the letters a or b as a tick mark and the letter r as a cross. In the following screenshot you can see that column F has been formatted with Marlett and the letters r or a have been entered as values. These entries can be used in a Sumif or Sumifs formula without any further ado.
