Totaling the Number of Checked Boxes

[Snoopymk37]

Hi there,

I have created a list of criteria for my students and then added check boxes. I want to automatically total how many students have followed each one. I have seen replies based on having a value in the column beside the box, but I just want to total the number of checks in each row. Any suggestions?

Thanks,

Sean

[AlKey]

Well, all check boxes have a linked cell to which they output values like TRUE and FALSE. Use COUNTIF function to count.

[Snoopymk37]

Thanks for the quick reply. You may have to walk me through this if possible.

Thanks again

[AlKey]

I can't do this without seeing the workbook. Do you know the range of the linked cells?

[Snoopymk37]

B2 to AB2 is one of the rows

[AlKey]

Try this formula

=COUNTIF(B2:AB2,"TRUE")

this will count all true when checked provided that that check boxes were set up to show "TRUE" when checked and "FALSE" if not.

[Tony Valko]

Well, all check boxes have a linked cell to which they output values like TRUE and FALSE.

You have to set the linked cell. It doesn't happen automatically.

[AlKey]

You have to set the linked cell. It doesn't happen automatically.

That is unknown

[sourabhg98]

Hello
Just follow these steps
1. Press Alt F11
2. Visual Basic Window will appear....Click insert>>>Module>>and paste the following code

Sub LinkCheckBoxes()
Dim chk As CheckBox
Dim lCol As Long
lCol = 25 'number of columns to the right for link

For Each chk In ActiveSheet.CheckBoxes
With chk
.LinkedCell = _
.TopLeftCell.Offset(0, lCol).Address
End With
Next chk

End Sub

3. Now press F5 to run this code....This code automatically assigns a cell link for each checkbox in the columns AA:AZ...now close this window and in your spreadsheet see columns AA:AZ they will show true or false depending upon the respective checkbox....

4. Now paste this formula in the column where you want the results and copy it downwards ..

=COUNTIF(AA2:AZ2,TRUE)

It will give you the result for each row

Hope it helps
Ask for further doubts

Regards
Sourabh Gupta

[Tony Valko]

That is unknown

It's unknown if the OP has set linked cells.

My reply was to inform them that the linked cell doesn't just happen automatically. You have set the linked cell a certain way depending on what type of checkbox you're using.

Judging from the screencap, it looks like the checkboxes are ActiveX checkboxes.

To set an ActiveX checkbox goto the Developer tab>click the Design Mode button. Right click on a checkbox>select Properties>select LinkedCell>enter the desired cell address for the linked cell

Close the Properties userform
Goto the Developer tab>Click on the Design Mode button to exit design mode.

[sourabhg98]

If you like my answer then please encourage me by pressing add reputation below * thanks.....

[Snoopymk37]

sourabhg98

Here is the file that you requested. Thanks. Once I see what you have done, I may be able to study it to see how it was fixed.

[sourabhg98]

Heres the workbook
I think that was my mistake....it should have been 28 in the formula instead of 25
Sorryyy......
I have done the changes and removed the macro as it is of no use now....it was only to create links

[Snoopymk37]

Thanks for looking at this. It is not totaling the checks in each row and then adds the word TOTAL in cells further to the right of the spreadsheet. I have attached a model of what I am trying to get it to look like.

[AlKey]

First you need to make small change to the VBA code: replace 25 with 0 in the line below, close module and re-run script by pressing F5 This will assign linked cells from B2 to AB2 for all check boxes. After that you can make them invisible in the cell by formatting all cells behind check boxes as Custom, and type in ;;;

lCol = 25 'number of columns to the right for link

now in cell AC2 you can use formula

=COUNTIF(B2:AB2,TRUE)

Please see attache file

[Tony Valko]

Another option is to just enter an X in the cells. You could either type them in manually or use an event macro to just double click on the cell, or simply by selecting a cell and the X will be entered automatically.

Then you'd count the number of Xs in the range.

=COUNTIF(Range,"X")

[Snoopymk37]

Thanks so much everyone. I really appreciate your time and patience.

Sean

[AlKey]

You're welcome and thank you for the feedback!

Please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools -> Mark thread as Solved).