Microsoft Excel - Total number of 'x' s across worksheets

Asked By Paul on 09-May-11 04:28 AM
Hi
I have setup an excel worksheet that simply requires an installer to place an x in the box if they are qualified in that particular skill. I have 20 sheets in the workbook, how do I formulate the book so that it will tell me how many installers can do a particular task without having to go through each sheet.

Candidate Assessment Form
Technology Type Install Commission Maintain Technology Type Install Commission. Maintain
Submarine systems       Power x    
PFEs (submarine networks)       Ethernet x    
SDH Optical Fibre Submarine Systems       SDH Systems x    
PDH       DWDM Systems      
ATM       21CN x    
Routers (Cisco)       LLU x    
Switches (Cisco)       DSLAM x    
 
Vendor/Equipment Install Commission Maintain Vendor/Equipment Install Commission. Maintain
Nortel Optera       Fujitsu FDX Copper MSAN x    
Nortel TN-x       Fujitsu FBX Fibre MSAN      
Nortel UE3000       Fujitsu Evotam x    
Nortel PDMXE       Huawei Optical      
Nortel CS2K       Huawei 21Cn CMSAN x    
Alcatel Lucent ISAM x     Huawei FMSAN x    
Alcatel Lucent ADM       Huawei WMSAN      
Alcatel Lucent Stinger       Tyco Evotam x    
FTel TAM x     ADVA FSP3000      
 
Skill Y/N Skill Y/N
Installation Y Gun wrapping Y
Commissioning   100-pair cable install Y
Fault diagnosis   Cable install incl Cat5e, Cat6, Cat7 Y
Fault rectification   Cable termination Y
Project management   Fibre optic install  
Equipment maintenance   Fibre splicing  
 
Other Comments
Manufacturers certifications  
Security cleared  
Asbestos Awareness training Y
First Aid Y
BT Working at heights regulations Y
Diary/site sign off reporting skills Y
UK Driving licence/Passport Y
CSCS number & expiry date Y
Internet ready Laptop Y
PPE, steps, signs, asbestos kit, barriers etc. Y
Ravi S replied to Paul on 09-May-11 04:42 AM
HI

Try with this sample example

To count the number of X's in a single cell:
=len(a1)-len(substitute(a1,"X",""))

To count the number of X's or x's (upper or lower) in a single cell:
=len(a1)-len(substitute(upper(a1),"X",""))

To count the number of X's in a contiguous range:
=sumproduct(len(a1:a10)-len(substitute(a1:a10,"X","")))

To count the number of X's or x's in a contigous range:
=sumproduct(len(a1:a10)-len(substitute(upper(a1:a10),"X","")))

refer the link for more examples
http://support.microsoft.com/kb/214153
http://support.microsoft.com/kb/187667

Paul replied to Paul on 09-May-11 05:16 AM
Hi Sam
Thanks for your reply, however I am not that clever. Where do I put the suggested formula? do I need to set up another sheet for all the totals? e.g. if say I wanted to know how many installers can do power (G3 on the example) how do I set that up?

Apologies for being ignorant.

Regards

Paul
wally eye replied to Paul on 09-May-11 11:29 AM
I found this on the BigResource (http://excel.bigresource.com/Track/excel-jhB7G2wb/)

=SUMPRODUCT(COUNTIF(INDIRECT("'"&DetailSheets&"'!B"&ROW(B3)),"Y"))

Where the named ranged "DetailSheets" has a list of the sheet names you want to include in the count, and you are counting the "Y''s in B3.
Paul replied to wally eye on 10-May-11 04:37 AM
Hi
I found this formula worked =COUNTA('sheet1:sheet15'!G3,"x") the only problem is that for some reason excel adds an extra 1 on the summary sheet. e.g. if there are 10 'x's in G3 across the workbook then excel makes this 11. Is there any way to resolve this?
Thanks
Paul
wally eye replied to Paul on 10-May-11 10:31 AM
CountA counts the number of cells that are not empty, the "x" at the end of your statement is essentially an extra non-empty value to count.  Take it off and you should be able to count the "x"'s in a particular cell.  In the lower part of your form you could change the Y/N to x/blank and it would work there.

The formula I gave you should work for counting the Y's and N's, you can tweak it a bit if you are just using "Sheetx" names:

=SUMPRODUCT(COUNTIF(INDIRECT("'Sheet"&ROW($1:$15)&"'!B"&ROW(B3)),"Y"))

It should autofill down, you will need to change the !B if you want to use it in a different column.