COUNT OPTION BUTTONS CHECKED

L

New Member
Joined
Jan 17, 2005
Messages
3
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

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
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

New Member
Joined
Jan 17, 2005
Messages
3
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

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
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

New Member
Joined
Jan 17, 2005
Messages
3
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

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
Sorry for the delay -- I've been on holiday :cool:
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
 

Forum statistics

Threads
1,147,688
Messages
5,742,626
Members
423,744
Latest member
bkirtland

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top