Counting occurrences over range of sheets

[DailyRich]

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

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

Any help would be appreciated

 

[Peo Sjoblom]

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

 

[DailyRich]

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

 

[Domenic]

Try...

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

or

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

....where D131 contains the sheet names.

Hope this helps!