COUNT OPTION BUTTONS CHECKED

[L]

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.

 

[SydneyGeek]

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.

 

[L]

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?.

 

[SydneyGeek]

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:
=COUNTIF(Grades, 1) for Grade 9
=COUNTIF(Grades, 2) for Grade 10
=COUNTIF(Grades, 3) for Grade 11
=COUNTIF(Grades, 4) for Grade 12

Hope that helps

Denis

 

[L]

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.

 

[SydneyGeek]

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:
=COUNTIF(Grades, 1) for Grade 9
=COUNTIF(Grades, 2) for Grade 10
=COUNTIF(Grades, 3) for Grade 11
=COUNTIF(Grades, 4) for Grade 12

Try that, see how you go

Denis