How do I total range of cells that have checks in checkboxes?

I have created a series of checkboxes from the Forms Toolbar. I want to get a
total of the number of boxes that have checkmarks in them.

Suppose that your first checkbox is in Cell C3 and that you have checkboxes
going down the "C" column until cell C13, so you will have 10 checkboxes.
Now, you are going to link each checkbox to a cell. I like to link them to
the cell that they are placed over, so I am going to link [Check Box 1] to
cell C3, [Check Box 2] to cell C4, [Check Box 3] to C5 and so on until I
have associated all of my checkboxes with a cell. Here is the procedure to
do this:



1. Right-click on the checkbox that you want to set properties for

2. Click on the last tab named "Control"

3. Type in the name of the cell that you want to associate with the
control box in the

"Cell Link" box.

4. Click "OK" and repeat this for all of the checkboxes.



So we have all of our checkboxes linked to a cell. Since it is difficult to
select a cell when there is a checkbox covering it, click in an open cell
and cursor into the first cell in your checkbox list, in this case, C3.
Type the following in the formula bar:



=IF(CheckBox1,1,0)



For cell C4, the formula is going to be:



=IF(CheckBox2,1,0)



Where the "=" notifies Excel that this is a formula that it must calculate;
IF is a built-in keyword/formula/function in Excel; then CheckBox1 is the
name of the control associated with that cell, 1 is the value that will be
placed in that cell if the box is checked, 0 is the number that will be
placed in that cell if the box is unchecked. The formula would read in
"English", If the checkbox control named CheckBox1 is checked, then set the
value of its associated cell, C3, to 1; otherwise, set the value of C3 to 0.



NOTE: Depending on the version of Excel, 1 or 0 may show up in the cell or
TRUE or FALSE respectively may show up. When performing arithmetic
calculations, like addition, TRUE represents the number 1 and FALSE
represents the number 0.



Now, select the two cells together, in this case, C3 and C4. At the bottom
right-hand corner of the selection, there will be a bold square corner, left
click this corner and drag the selection box all the way to the end of your
checkboxes, so I would drag it all the way to cell C13. This will use
Excel's "smarts" to fill in the remainder of the cells based on the pattern
that you started.



Finally, decide which cell you want the total number of checks to appear in.
In that cell, type the following formula:



=SUM($C3:$C13)



This will give you the sum of the cells from C3 (or whichever cell you are
starting with) to your ending cell, in my case, C13. The $ in front of the
C is only a good practice, not necessary here, but it ensures that if you
drag this formula to fill other cells based on its pattern, the column C
will never change, only the row numbers will change, because it is not
preceded by a $.



If you want to, you can select all of the cells behind the checkboxes and
change the text color to white, that way the numbers won't show up as long
as you have a white background.



I hope this helps you out. I am using the next Beta of MS Excel, so if
there is anything different, or if you get a formula error or anything,
please let me know, and I'll get you any help or corrections that you need.



~Rob







"instructorjml" <instructorjml@discussions.microsoft.com> wrote in message
news:BC685AEF-754B-407B-8B7B-C18D2070DE81@microsoft.com...
>I have created a series of checkboxes from the Forms Toolbar. I want to get
>a
> total of the number of boxes that have checkmarks in them.

"~RobW" wrote:

....
> =IF(CheckBox2,1,0)
....
> NOTE: Depending on the version of Excel, 1 or 0 may show up in the cell or
> TRUE or FALSE respectively may show up. When performing arithmetic
> calculations, like addition, TRUE represents the number 1 and FALSE
> represents the number 0.

In what version of Excel would this return a boolean? In my Experience,
Excel is quite careful about returning the requested type; though it will
coerce TRUE to 1 and FALSE to 0 if you then ask it to do math with them.

Jerry