add multiple cells ignoring #n/a cells

[jbillyo]

I have a spreadsheet that needs a running total at different points in the year. The formula in the cells returns #n/a when it has no value. Is it possible to use a formula that will ignore the #n/a value and still return a number.

example

a1 = 10
a2 = #n/a
a3 = #n/a
a4 = #n/a
a5 should return 10

then

a1 = 10
a2 = 2
a3 = #n/a
a4 = #n/a
a5 should return 12

Any help would be greatly appreciated.
Thanks in advance

[RobertMika]

=sumif(a1:a4,"<>#n/a")

[jbillyo]

thank you so much for the quick reply. Worked a treat!

[RobertMika]

You are welcome.

[Tony Valko]

Here's another way...

If there won't be any negative numbers in the range:

=SUMIF(A1:A4,">0")

If there might be negative numbers in the range:

=SUMIF(A1:A4,"<1E100")

[jbillyo]

just on the off chance, is it possible to add a formula to a cell's original formula that will stop #n/a showing?
Thanks in advance

[jbillyo]

Thank you Tony as well...I really have a lot to learn!!!

[Tony Valko]

Yeah, me too!

You're welcome!

[Tony Valko]

Yes.

Without seeing the formula, a generalized method that returns a blank instead of an error:

=IFERROR(your formula here,"")

[jbillyo]

Hi Tony,

This is what is in one of the cells

=VLOOKUP(M6,Lookup!$C$4:$D$40,2,FALSE)

I have tried to add it to the generalized method but it doesn't seem to work
Thanks for any help

[Tony Valko]

Try it like this...

=IFERROR(VLOOKUP(M6,Lookup!$C$4:$D$40,2,0),"")

The 0 is a shorthand equivalent of FALSE.