Couting occurances of a word across multiple worksheets

wmsbutler

New Member
Joined
Oct 11, 2006
Messages
3
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?

Thank you for your help in advance.

Will
 

Some videos you may like

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
Joined
Sep 30, 2005
Messages
1,218
Something like;

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

Any use?
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,362
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
Joined
Oct 11, 2006
Messages
3
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!

Thank you in advance,

Will
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,362
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,111,493
Messages
5,541,041
Members
410,543
Latest member
ExcelGlenn
Top