You'll need to explain what you're trying to do.

--
Biff
Microsoft Excel MVP


"Brigette" wrote in message
...
After reading these messages I still could not get it to work. please help

"T. Valko" wrote:

Try this:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&$H$1:$H$7&"'!B"&R OWS(A$1:A7)),"x"))


--
Biff
Microsoft Excel MVP


"janee" wrote in message
...
THIS WORKED !! Thank you so much ! I fixed the formula where the
H1-H7
remained constant when I copied the formula down the page. However,
when
I
copy the formula down the page, it is still looking at B7 in all of the
cells, instead of B8, B9, B10, as it goes downw the page. How can I
make
it
change the cells?

=SUMPRODUCT(COUNTIF(INDIRECT("'"&$H$1:$H$7&"'!B7") ,"x"))

Thanks again
Janee


"Peo Sjoblom" wrote:
You need to create a list with all the sheet names, like:

Smith
Jones
etc

Note that you need all sheet names, or else it won't work

then assume you put the sheet names in H1:H7

use

=SUMPRODUCT(COUNTIF(INDIRECT("'"&H1:H7&"'!B7"),"x" ))

Regards,

Peo Sjoblom