You can do it by putting all the sheet names to add in a list in say
M1:M100, and then use

=SUMPRODUCT(COUNTIF(INDIRECT("'"&M1:M100&"'!D2"),0 ))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Slim" wrote in message
...
Thanks Bob, but it is not quite what I need. I obviously need to supply
more
details -

What I have is a spreadsheet containing over 120 worksheets. The last
sheet
is a template sheet used to create new case sheets from. The penultimate
sheet and the second sheet are 'blank' sheets used to make some data
collation formulae work properly. (I actually have half a dozen of these
spreadsheets, each created from a master template. Hence the reason for
using
blank sheets so that I did not get a load of errors when there were no
case
sheets in the empty files).
The first sheet in the file is a data sheet that each of the 120+ case
sheets references for various values. Each case sheet perfroms various
calculations and then references a lookup table on the data sheet to get
an
integer value (depending on the value of the calculations), ranging from 0
to
40 or a dummy value -98. The integer value is stored on each case sheet in
cell D2. The two blank sheets store the dummy value -99 in cell D2.
What I am trying to do is count up how many case sheets have the value 0
in
cell D2, how many have the value 1 in cell D2, etc, and display this data
on
the first sheet - the original data sheet. Hence the countif formulae

=COUNTIF('Blank Sheet:Blank Sheet 2'!D2,"=0")
=COUNTIF('Blank Sheet:Blank Sheet 2'!D2,"=1")
=COUNTIF('Blank Sheet:Blank Sheet 2'!D2,"=2")
=COUNTIF('Blank Sheet:Blank Sheet 2'!D2,"=3")
=COUNTIF('Blank Sheet:Blank Sheet 2'!D2,"=4")
etc, etc...

Unfortunately, all I get is the #VALUE! error when I use any of the basic
COUNTIF formulae listed above.

I have been able to use the COUNT function on a specific cell in each of
the
case sheets between the two blank sheets, but not the COUNTIF function.


Hopefully this all makes sense and I have given you enough information to
help me...


Cheers,

Slim.

"Bob Phillips" wrote:

Check my response!

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Slim" wrote in message
...


"Otto Moehrbach" wrote:

Slim
You have to give us more than that. What is your question?
HTH
Otto
"Slim" wrote in message
...



I am trying to use the COUNTIF function over a number of worksheets
in a
spreadsheet to counht various values in a given cell on each sheet.
However,
I just get a #VALUE error.
An example of the formula I am trying is
=COUNTIF('Blank Sheet:Blank Sheet 2'!D2, "0")

Any help much appreciated.