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