# COUNT OPTION BUTTONS CHECKED

#### L

##### New Member
I have an Excel workbook with 31 worksheets (summary, 1, 2, 3….30). Worksheet 1-30 all have the same group box named grade level with four option buttons named 9, 10, 11, and 12. A spreadsheet is assigned to individual students to input student information and grade level.

I need to use the Count feature to count the # of times the 9, 10, 11, and 12 "Option buttons" are checked. I want to place this information in the summary worksheet.

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Hi L,

I tried a dummy run which worked as follows:
1. Create your option buttons -- I used the ones from the Control Toolbox. You need to go into View Code, then show the Properties window to set the caption and the linked cell (Sheet1!A1, etc). What I did was to use the same cell sequence in each sheet (A1 = Grade 9, A2 = Grade 10, etc. Use 4 out of the way cells for this). This is the tedious bit. The output for these option buttons is TRUE or FALSE.
Note: you need to be specific about the references: Sheet1!A1, then Sheet2!A1 for the Grade 9 option on the next sheet, etc.
2. Create four 3-D ranges -- Select the first sheet tab, Shift and select the last (exclude the summary sheet). For each range select the desired cell (eg, A1) and in the Name box, type (eg Grade9) followed by the Enter key.
3. Now, on the summary sheet, you need 4 calcs -- =COUNTIF(Grade9,TRUE) and so on for the other grades. With nothing selected you'll have zero values. Now go through the sheets, ticking off the grades, and check the result.

The above is a fairly condensed descsription. Please post back if you need any more detail.

Thank you - I am not too familiar with the view code/properties window.

The Option Buttons were created to be inside a Group Box because I want the student to only select one grade. I created the Group Box and Option Buttons using the Forms toolbox. The Option Button are not linked to a particular cell.

In sheet1 I have more than one group box with Option Buttons and Check Boxes that require me to use the COUNT feature when each contains a checked option. If the Group Box floats over cells B5:F9I and the Option Buttons are inside this box, then how can I link to a cell?.

OK, here's the version for the Forms toolbox.

Create the Group boxes as you have. Right-click in the Group box, select Format Control. Now on the Control tab, enter an address for the Link Cell.
When I did it, I got 1,2,3,4 for the respective controls, appearing in the Link Cell. On each sheet, you should get the same result.
Once that's done, say you used AA1 as your Link Cell on all sheets. Create a single 3-D range as I described before. Call it Grades.
On the Summary Sheet, the formulas now are:

Hope that helps

Denis

Ok - I tried your a dummy run with your instructions. I I am confused in creating the four 3-d ranges. I will appreciate if you can expand on your instruction for #2 above.

Sorry for the delay -- I've been on holiday
Here's the instruction set in detail:

OK, here's the version for the Forms toolbox.

1. Create the Group boxes as you have. (You should have 1 Group box with the option buttons on each sheet).

2. For EACH SHEET -- Right-click in the Group box, select Format Control. Now on the Control tab, enter an address for the Link Cell. This is the cell where the data will go when you make a selection. For example. make it BA1. Note: Excel automatically assigns the output values 1,2,3,4... to Option Buttons as you create them on each sheet. So, make sure that Grade 9 is the first, Grade 10 is the second, etc. On each sheet, you should get the same result.

3. Once that's done, say you used BA1 as your Link Cell on all sheets. Create a single 3-D range as I described before. To create the range (you don't need 4 ranges if you use the Forms toolbox with grouped controls) --
a. Click sheet 1, then shift-click sheet 30. You'll see all the tabs highlighted in white, and the word "Group" will appear in square brackets in the title bar.
b. Select cell BA1, then in the Name Box (it'll have the cell's address displayed) type the word Grades Press ENTER, and you've created the 3-D range.

4. On the Summary Sheet, the formulas now are:

Try that, see how you go

Denis

Replies
20
Views
566
Replies
1
Views
154
Replies
3
Views
167
Replies
2
Views
172
Replies
4
Views
363

### Forum statistics

1,221,005
Messages
6,157,341
Members
451,415
Latest member
LettersFromSeneca

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back