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")