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