Recognise Valid Numbers Only

[bnc123]

I have some numbers and some uncalculated formulas as follows:

A1: 123
A2: 567
A3: #VALUE!
A4: 856
A5: #DIV/0!

Now, I want to add them all up by using the sum formula

=SUM(A1:A5)

but I want to add only the valid numbers (123, 567 and 856) and ignore the uncalculated formulas (#VALUE! and #DIV/0!).

Any way to do that?

 

[macropod]

Hi BNC,

Try:
=SUM(IF(ISERROR(A1:A5),0,A1:A5))
entered as an arry formula (Ctrl-Shift-Enter)

Cheers

 

[bnc123]

Thanks macropod, it did work.

Now what do I do if I want to add the same cells as:
=A1+A2+A3+A4+A5. What do I do then? I tried to use

=(IF(ISERROR(A1+A2+A3+A4+A5),0,A1+A2+A3+A4+A5))

but it didn't work.

This is the way I really want to do it. I think I asked the wrong question in the first place. Sorry about that.

 

[macropod]

Hi BNC,

In that case, you could use a standard formula like:
=IF(ISERROR(A1),0,A1)+IF(ISERROR(A2),0,A2)+IF(ISERROR(A3),0,A3)+IF(ISERROR(A4),0,A4)+IF(ISERROR(A5),0,A5)

Cheers

 

[bnc123]

Many Thanks macropod. It worked.

You have just saved the day.

Best Regards.