If you mean that you want D4 to increment to D5 and so on?

SUMPRODUCT(COUNTIF(INDIRECT("'"&MySheets&"'!"&CELL ("address",D4)),"0"))

is you use a range with your sheet names make sure it has absolute reference
like

$H$2:$H$25

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)



"Fysh" wrote in message
...
Never mind I used 0 and it seems to work. But I still have to change the
last part for each cell.

"Fysh" wrote:

Ok Like I said before your solution worked. However, I have a couple
questions. First how do you use this if the cell is null or has N/A it,
to
not include these when divding? Second how do you drag the formula to
the
other cells? For some reason I have to go in and edit each cell on the
main
worksheet.

Thanks for any input.

"Peo Sjoblom" wrote:

You can't use countif over multiple sheets, there is a workaround but
it
requires some extra work

=SUM('PSNS:TRF
KB'!D4)/SUMPRODUCT(COUNTIF(INDIRECT("'"&MySheets&"'!D4")," <0"))

where MySheets is a named range that holds a list of ALL you sheet
names
that you want to include, not only the first and last sheets like in
the SUM
formula, you need to put all the sheet names. Of course it does'nt have
to be
a named range, if you have 25 sheets you can use for example

=SUM('PSNS:TRF
KB'!D4)/SUMPRODUCT(COUNTIF(INDIRECT("'"&H1:H25&"'!D4"),"< 0"))



Regards,

Peo Sjoblom

"Fysh" wrote:

I was hoping someone could assit me on this. I have several
workbooks which
update a main workbook. On this workbook I have several worksheets
with a
main worksheet, which I am trying to gather averages of several
cells.

I am using the reference name function for the main workbook. So even
though
the cell shows a value if I click on the cell, the formula bar shows
=name
which is associated with it.

I don't know if what I just mentioned has anything to do with it, but
I have
tried several different formulas but none seem to work. I keep
getting
#VALUE!

Here is one which doesn't seem to work. Could someone assist me on
this?
Thanks

=SUM('PSNS:TRF KB'!D4)/COUNTIF('PSNS:TRF KB'!D4,"<0")