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
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

JazzSP8

Well-known Member
Joined
Sep 30, 2005
Messages
1,223
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
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,721
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,721
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!
 

Forum statistics

Threads
1,141,153
Messages
5,704,607
Members
421,359
Latest member
Edwardvanschothorst

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top