Counting occurrences over range of sheets
 

I have a workbook that consists of a sheet for every day plus a summary
sheet. Each daily sheet is a sign-in log where I enter names of people
who did not sign out a certain piece of equipment under an OPEN or CLOSE
column. I then enter each name on the summary sheet. I need a formula
that will search through all 31 daily sheets in only the OPEN and CLOSE
columans and count each time a name on the summary sheet appears.

I had a rather unweildy SUMPRODUCT formula that basically had an
argument for each individual page and which checked the entire sheet,
but altering it to only check certain columns would make it way too
long. And when I try to do it as a range of sheets ('1:31'!) I get a
VALUE error and it appears to be checking cells outside the range
(something like $BC$1) when I show the calculation steps.

Any help would be appreciated!


--
DailyRich
------------------------------------------------------------------------
DailyRich's Profile: http://www.excelforum.com/member.php...o&userid=30284
View this thread: http://www.excelforum.com/showthread...hreadid=499528

Counting occurrences over range of sheets
 

If they actually are called 1, 2 and so on you should be able to use

=SUMPRODUCT(COUNTIF(INDIRECT("'"&ROW(INDIRECT("1:3 1"))&"'!A1:A1000"),"name")
)

if not you need to put the names of ALL sheets in a range and the refer to
that range like in

=SUMPRODUCT(COUNTIF(INDIRECT("'"&H1:H31&"'!A1:A100 0"),"name"))

adapt to fit accordingly


--

Regards,

Peo Sjoblom

"DailyRich" wrote
in message ...

I have a workbook that consists of a sheet for every day plus a summary
sheet. Each daily sheet is a sign-in log where I enter names of people
who did not sign out a certain piece of equipment under an OPEN or CLOSE
column. I then enter each name on the summary sheet. I need a formula
that will search through all 31 daily sheets in only the OPEN and CLOSE
columans and count each time a name on the summary sheet appears.

I had a rather unweildy SUMPRODUCT formula that basically had an
argument for each individual page and which checked the entire sheet,
but altering it to only check certain columns would make it way too
long. And when I try to do it as a range of sheets ('1:31'!) I get a
VALUE error and it appears to be checking cells outside the range
(something like $BC$1) when I show the calculation steps.

Any help would be appreciated!


--
DailyRich
------------------------------------------------------------------------
DailyRich's Profile:
http://www.excelforum.com/member.php...o&userid=30284
View this thread: http://www.excelforum.com/showthread...hreadid=499528

Counting occurrences over range of sheets
 

That's awesome, thanks a lot!

Now, is there a way to have it look through more than one set of
ranges? It works for one range (the A1:A1000 part), but I need it to
look through three or four different ranges on each sheet (say A1:B20,
D1:E20, and G1:G20).


--
DailyRich
------------------------------------------------------------------------
DailyRich's Profile: http://www.excelforum.com/member.php...o&userid=30284
View this thread: http://www.excelforum.com/showthread...hreadid=499528

Counting occurrences over range of sheets
 

Try...

=SUMPRODUCT(COUNTIF(OFFSET(INDIRECT("'"&ROW(INDIRE CT("1:31"))&"'!A1:A20")
,,{0,3,6},,{2,2,1}),"Name"))

or

=SUMPRODUCT(COUNTIF(OFFSET(INDIRECT("'"&D1:D31&"'! A1:A20"),,{0,3,6},,{2,2
,1}),"Name"))

....where D1:D31 contains the sheet names.

Hope this helps!

In article ,
DailyRich
wrote:

That's awesome, thanks a lot!

Now, is there a way to have it look through more than one set of
ranges? It works for one range (the A1:A1000 part), but I need it to
look through three or four different ranges on each sheet (say A1:B20,
D1:E20, and G1:G20).