Counting number of times a word appears across worksheets

**danltd** · 10-22-2012, 09:49 AM

Hi ,

Basically I have created a spreadsheet with details of several different people for HR purposes. Each person is assigned to a worksheet called 'People Info' and I have 70 copies of that worksheet with each persons details on. One of the criteria is each persons work status which has a drop down list with the words: Permanent, FTC, Apprentices etc.

I also have a sheet called 'Summary' with the headings : Permanent, FTC, Apprentices, etc. Which i am using to count the totals of how many Permanent/ FTC/Apprentice staff there are altogether.

Basically I am trying to create a formula for the 'Summary' sheet which counts how many times the word Permanent/FTC/Apprentice appears altogether, but this needs to count across worksheets. The data is on the same cell (A11) in each of the worksheets but I just cant work the formula out, can anyone help?

Cheers

**Miraun** · 10-22-2012, 10:01 AM

To the best of my knowledge, there's no "Across all sheets" type Excel function, unless you wanted to go to VBA and update it with a macro-button...

That being said, you can add it up for each of the other pages manually using

=Sheet1!A11+Sheet2!A11... but I know that will get old quick.

**danltd** · 10-24-2012, 04:33 AM

Thanks for your reply!

Ok I might have to do it manually, however I thought the function =sumproduct could be used across sheets?

edit:

Also I need the formula to only count the word 'Permanent' in the cell A11, as it is a dropdown list it will have other words which wont need to be counted.

Counting number of times a word appears across worksheets

LinkBack

Thread Tools

Rate This Thread

Display

Counting number of times a word appears across worksheets

Re: Counting number of times a word appears across worksheets

Re: Counting number of times a word appears across worksheets

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions