Can You 'count' check boxes and add the sums on another sheet?

[wisedave]

Hello,

I am using Excel 2010. I have a sales spread sheet that I input some basic info and price of different products. The first sheet is a 'total' page that shows the totals of each sheet and a grand total. I call it a snapshot of the sales.

On each sheet, there are tick boxes for various reasons (i.e. online sale, payment plan, book only, external candidate). Not every sheet has each option.

I would like to have the first page (snap shot) show the total number of boxes ticked for each category of box. For example, if there are 10 'online sales' from all of the sheets, I would like to have the front page show me "Buy Onlines - 10".

Is this possible to do?

I have attached the workbook I am working with so you can see what I am up against.

Thanks for any help!

Cheers,

Dave

[wisedave]

Hello,

Does anybody know how to solve this one? I could use a little direction......

Thanks for the help!

Cheers,

Dave

[protonLeah]

Your workbook is difficult to work with due to the many merged cells:

It was necessary to slightly reformat it to make the following easier:


1. I deleted the check boxes and formatted the two columns with Marlett font.

2. I added a doubleclick event handler to the THISWORKBOOK module. It checks to verify that either of the two columns, Buy... & Pmt have been clicked and inserts "a" in the cell which Marlett graphs as a checkmark.

3. I entered a countA formula in row 33. This value is copied to the Totals sheet where I unmerged the cells and added two columns.

[wisedave]

Hello Ben,

First off let me apologise for not responding.....it must have slipped throught the cracks! Second....that's a cracking effort!

Let me have a fiddle with this. One thing I can see that I need some direction on the NGC Classroom and IOSH M S eLearning don't have the totals on the front even when I tick the box. Also is there a way to have the grand totals at the bottom in yellow?

If you could guide me on a fix that would be great! If it's easier to pop in and change it yourself then upload it...even better. Either way I appreciate any effort you put into this!


Thanks for the help!

Cheers,

Dave

[protonLeah]

Look at this one:

[wisedave]

How do you do that? Excel is way more powerful than I ever expected. How do I get the IOSH Managing Safely eLearning on the front page to have the totals?

You're a star for helping me sort this out.

Many thanks,

Dave

[protonLeah]

On your WISE TOTALS sheet, most of the names in the summary rows are the same as the names of the sheets where the totals are copied from. But, the IOSH Managing Safely eLearning summary is coming from the sheet named IOSH eLearning. I did not realize before that they were the same sheet. The formula I used on the WISE TOTALS cells is:

=IFERROR(INDIRECT("'"&TRIM($A5)&"'!E33"),"")

that formula builds the sheet name by taking the name in Column A (NEBOSH NGC eLearning in this case), surrounding it with single quotes and concatenates the cell address holding the counts. (E33 and F33). Since the name on the Totals sheet did not match the actual sheet name, the formula won't work. You'll have to replace it with:

Please Login or Register  to view this content.

on the Totals Sheet. On the IOSH eLearning sheet, cell E33 need to have: =COUNTA(E9:E32) to count the check marks entered, similar for F33.
You could do all the summary counts that way. I only used the longer formula because I wrote it once and dragged it down and across, saving typing.

[wisedave]

Hello,

Thanks again for the direction! I will have a play with this.....

Rep given!

Cheers,

Dave