I had suggested

=IF(SUMPRODUCT(--(B78:B94<0), (B78:B94)),SUMPRODUCT(--(B78:B94<0),(B78:B94)),"#N/A")

Did you try that AFTER changing NA() to #N/A in your formulae?
Test it out on a new sheet with few cells

"BNT1 via OfficeKB.com" wrote:

Hi Sheeloo

Unfortunately, this has not fixed my problem as I expected. Have amended all
files to show #n/a instead of NA()

How can I get the cell to return a #n/a if no values are in the range

I am using this formula {=sum(if(isnumber(B78:B94),B78:B94))} which returns a
0, when no values (except #n/a are in the range). I want it to show #n/a

regards


Sheeloo wrote:
#N/A entered directly is just text...
=NA() however returns 'Not a value' ...

Pl. mark the post as answered if you got what you needed.

Hi Sheeloo

[quoted text clipped - 21 lines]

Brian

--
Message posted via http://www.officekb.com