SUM() of empty dynamic named range returns #N/A

[Aviashn]

I have a cell with =SUM(CamperRev) where CamperRev is a dynamically
named range containing contribution amounts. This works fine if there
are any contributions, but displays #N/A if there are not.

I worked around this with
=IF(ISNUMBER(SUM(CamperRev)),SUM(CamperRev),"") but was wondering if
there is a better way.

 

[Dave Peterson]

=sum() ignores text and empty cells.

So if you got an error from the =sum() function, that means that you have an
error in that range.

You could use an array formula:
=sum(if(isnumber(camperrev),camperrev))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

And camperrev can't refer to a whole column except in xl2007.

Or you could change the formula that is used in the cells in camperrev. Instead
of returning an error, you could return a 0 or an empty string ("").

 

[Aviashn]

Thanks for your response. The array formula is a superior solution, I
hadn't considerd using an array at all.

Your post (and solution) have still left me confused, however, about
the exact nature of the problem.

"=sum() ignores text and empty cells. So if you got an error from the
=sum() function, that means that you have an
error in that range. "
AND
"Or you could change the formula that is used in the cells in
camperrev."

All the cells are empty. No formulas or values. If a value is
entered it works fine.

"And camperrev can't refer to a whole column except in xl2007. "
camperrev refers to a dynamically named range using the following
formula:
=OFFSET(Contributions!$H$2,0,0,MATCH(1E+306,Contributions!$H:$H,1),1)

As a result, the range is only as large as the amount of entries.

Perhaps the problem is that in this case the range object returns
Nothing?