software

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: www.excelforum.com/member.php...oamp;userid=30284
View this thread: www.excelforum.com/showthread...hreadid=499528If they actually are called 1, 2 and so on you should be able to use

=SUMPRODUCT(COUNTIF(INDIRECT(quot;'quot;amp;ROW(INDIRECT(quot;1:3 1quot;))amp;quot;'!A1:A1000quot;),quot;namequot;)
)

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(quot;'quot;amp;H1:H31amp;quot;'!A1:A100 0quot;),quot;namequot;))

adapt to fit accordingly--

Regards,

Peo Sjoblom

quot;DailyRichquot; gt; wrote
in message news
gt;
gt; I have a workbook that consists of a sheet for every day plus a summary
gt; sheet. Each daily sheet is a sign-in log where I enter names of people
gt; who did not sign out a certain piece of equipment under an OPEN or CLOSE
gt; column. I then enter each name on the summary sheet. I need a formula
gt; that will search through all 31 daily sheets in only the OPEN and CLOSE
gt; columans and count each time a name on the summary sheet appears.
gt;
gt; I had a rather unweildy SUMPRODUCT formula that basically had an
gt; argument for each individual page and which checked the entire sheet,
gt; but altering it to only check certain columns would make it way too
gt; long. And when I try to do it as a range of sheets ('1:31'!) I get a
gt; VALUE error and it appears to be checking cells outside the range
gt; (something like $BC$1) when I show the calculation steps.
gt;
gt; Any help would be appreciated!
gt;
gt;
gt; --
gt; DailyRich
gt; ------------------------------------------------------------------------
gt; DailyRich's Profile:
www.excelforum.com/member.php...oamp;userid=30284
gt; View this thread: www.excelforum.com/showthread...hreadid=499528
gt;

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: www.excelforum.com/member.php...oamp;userid=30284
View this thread: www.excelforum.com/showthread...hreadid=499528Try...

=SUMPRODUCT(COUNTIF(OFFSET(INDIRECT(quot;'quot;amp;ROW(INDIRE CT(quot;1:31quot;))amp;quot;'!A1:A20quot;)
,,{0,3,6},,{2,2,1}),quot;Namequot;))

or

=SUMPRODUCT(COUNTIF(OFFSET(INDIRECT(quot;'quot;amp;D131amp;quot;'! A1:A20quot;),,{0,3,6},,{2,2
,1}),quot;Namequot;))

....where D131 contains the sheet names.

Hope this helps!

In article gt;,
DailyRich gt;
wrote:

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