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!

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

=SUMPRODUCT(COUNTIF(INDIRECT("'"&ROW(INDIRECT("1:31"))&"'!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:A1000"),"name"))

adapt to fit accordingly


--

Regards,

Peo Sjoblom

"DailyRich" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

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).

Try...

=SUMPRODUCT(COUNTIF(OFFSET(INDIRECT("'"&ROW(INDIRECT("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 <[email protected]>,
DailyRich <[email protected]>
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).