Sum a column after clicking a check box

[hsingh2088]

Hi,

I want to have the ability to select items on one column, and sum the items in a column directly adjacent to it. How do I build this formula/function in Excel?

 

[danzon]

Say your items are in column A...

insert a check box control alongside each item and link each to the corresponding column B cell...

then use
=SUMIF(B1:B10,"TRUE",A1:A10)

to add those column A value that have a checkmark

 

[hsingh2088]

OK I am a total noob to this, how do I add a checkbox?

 

[danzon]

In excel 2007 you will need to turn on your developer tab if it is not already visible...

Click OFFICE icon at the top left...

select EXCEL OPTIONS at the bottom right

in the "POPULAR" tab tick the "Show developer tab in Ribbon" box

hit OK....


you should now see the developer tab in your ribbon

select the developer tab, click INSERT, and you should see a FORM CONTROL check box... click it and then click in the spreadsheet where you want it to appear...

once you have it in your spreadsheet, right click on it and select FORMAT CONTROL... in the "CONTROL" tab there is a "Cell Link"... you will link each CHECK box to a unique cell ( which you can hide if you like ).... then when someone checks the box that cell shows TRUE and when the box is not checked that cell reads FALSE... you can use those TRUE / FALSE results in your formulas to achieve different results based on checkmark selections

 

[jrabi]

You can simply use the entire column, no need to use cel to cel much easier to use entire column in formula, see below:

=SUMIF(B:B,"TRUE",A:A)

 

[hsingh2088]

Neither of those options worked. When I click the check box, it erases the number I hard entered in the adjacent column, and puts in "TRUE" if the box is checked and "FALSE" if it's unchecked.

 

[danzon]

Neither of those options worked. When I click the check box, it erases the number I hard entered in the adjacent column, and puts in "TRUE" if the box is checked and "FALSE" if it's unchecked.

You are doing it wrong...

DO NOT llink your check box to the same cell that has your values... link it to the ADJACENT cell

so if your numbers are in column A, then link each check box to the adjacent column B cell..

then use the SUM IF to sum the column A values that have a TRUE in the adjacent column B cell

 

[hsingh2088]

That worked, thanks for all your help, much appreciated!