summing while #REF! present

[jayron]

I am trying to sum a row of numbers, but some of the numbers are currently displaying #REF!, because they are referring to something that is not yet created, but will be eventually so I want to keep it. My question is, can I somehow create a sumif(XYA<> #REF!) or some other way of including the cells with ref errors but not getting a ref error as a result.

i.e.

Cell A1: 4 Cell B1: #REF! Cell C1: 6 Cell D1: 7 Cell E1: 9

I want to sum A1:E1 and not get a #REF! error. Is this possible?

Thanks a lot....

[NBVC]

If you can't get rid of the REF! error, then try:

=SUM(IF(ISNUMBER(A1:E1),A1:E1))

where A1:E1 is the range to sum....

Note: Formula must be confirmed with CTRL+SHIFT+ENTER keys not just ENTER... you will see { } brackets appear.

[VBA Noob]

Or

=SUMIF(A1:E1,"<>#Ref!")

VBA Noob

[NBVC]

Or



VBA Noob

That works too