# Couting occurances of a word across multiple worksheets

#### wmsbutler

##### New Member
Hi,

I'm trying to create a timesheet in excel so I can get a better handle on how many hours each of my employees are spending on a particular client's business. Each employee has a worksheet within the overall workbook and they then select from a drop down list on an hourly basis the client they have been focused on. The drop down list is updated as and when new clients / projects commence.

On a worksheet at the back of this workbook I am trying to tally up the hours spent against each client by week. This involves a formula which needs to pick up the occurance of certain words across certain ranges of cells. Here is an example of the formula I am using to pick up work on one particular client across the course of a week ("Rumble" is the word I am looking to pick up).

=SUM(LEN(Anna!B2:H25)-LEN(SUBSTITUTE(Anna!B2:B25,"Rumbles","")))/LEN("Rumbles")+SUM(LEN(Bob!B2:H25)-LEN(SUBSTITUTE(Bob!B2:B25,"Rumbles","")))/LEN("Rumbles")+SUM(LEN(Dionne!B2:H25)-LEN(SUBSTITUTE(Dionne!B2:B25,"Rumbles","")))/LEN("Rumbles")+SUM(LEN(Emily!B2:H25)-LEN(SUBSTITUTE(Emily!B2:B25,"Rumbles","")))/LEN("Rumbles")+SUM(LEN(Helen!B2:H25)-LEN(SUBSTITUTE(Helen!B2:B25,"Rumbles","")))/LEN("Rumbles")+SUM(LEN(James!B2:H25)-LEN(SUBSTITUTE(James!B2:B25,"Rumbles","")))/LEN("Rumbles")+SUM(LEN(Martin!B2:H25)-LEN(SUBSTITUTE(Martin!B2:B25,"Rumbles","")))/LEN("Rumbles")+SUM(LEN(Ric!B2:H25)-LEN(SUBSTITUTE(Ric!B2:B25,"Rumbles","")))/LEN("Rumbles")+SUM(LEN(Simon!B2:H25)-LEN(SUBSTITUTE(Simon!B2:B25,"Rumbles","")))/LEN("Rumbles")+SUM(LEN(Will!B2:H25)-LEN(SUBSTITUTE(Will!B2:B25,"Rumbles","")))/LEN("Rumbles")

This is being entered as an array formula.

The problem is that this is working for "Rumble" but when I try to copy the formula into other cells and try to get it to search for other words it no longer does.

Is this the right forumla to be replicating? Is there an easier way?

Will

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

#### JazzSP8

##### Well-known Member
Something like;

Code:
``=SUM((COUNTIF(Anna!B2:H25,"*Rumble*"))+(COUNTIF(Bob!B2:H25,"*Rumble*")))``

Any use?

#### Domenic

##### MrExcel MVP
List the sheet names in a range of cells, let's say L2:L10, then try...

=SUMPRODUCT(COUNTIF(INDIRECT("'"&\$L\$2:\$L\$10&"'!B2:H25"),"*Rumble*"))

or

=SUMPRODUCT(COUNTIF(INDIRECT("'"&\$L\$2:\$L\$10&"'!B2:H25"),"*"&M2&"*"))

...where M2 contains Rumble.

Hope this helps!

#### wmsbutler

##### New Member
Fantastic, many thanks for the help! The sheet is now up and running #### wmsbutler

##### New Member
One further question now I've got the sheet up and running. Could you advise me of a faster way to update the elements of my forumla across cells.

E.g. I now have a column which totals up activity in week 1:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&\$A\$75:\$A\$84&"'!B2:H25"),"*Rumbles*"))
=SUMPRODUCT(COUNTIF(INDIRECT("'"&\$A\$75:\$A\$84&"'!B2:H25"),"*R&R*"))

etc... for each of the acitivites I want to cover.

Now for week 2, the formulas need to change to this:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&\$A\$75:\$A\$84&"'!I2:O25"),"*Rumbles*"))
=SUMPRODUCT(COUNTIF(INDIRECT("'"&\$A\$75:\$A\$84&"'!I2:O25"),"*R&R*"))

Given I have 42 different activities and need to update them all for 52 weeks of the year, an automated way of updating those cell references (e.g. B2:H25 to I2:O25) would be gratefully received!

Will

#### Domenic

##### MrExcel MVP
Let a range of cells, let's say B75:B116, contain the list of activities such as Rumbles, R&R, etc., then enter the formula in the target cell, let's say C75, copy the formula down and across...

=SUMPRODUCT(COUNTIF(OFFSET(INDIRECT("'"&\$A\$75:\$A\$84&"'!B2:H25"),,COLUMNS(\$C75:C75)*7-7),"*"&\$B75&"*"))

Hope this helps!

Replies
5
Views
5K