Sum the column with the error cells

[David]

I am trying to add the total for one column in which some cells have the
#REF! sign. My formula is:
=SUMIF(Data!A1ata!A100,"<>#VALUE!")
but the output is still #REF! and cannot display the value total. Does my
formula miss any?

 

[Luke M]

Your sheet reference is invalid (you truly do have a reference error).

=SUMIF(Data!A1:A100,"<>#VALUE!")

Of note, why are you checking for "#VALUE!"? should it be searching for the
"#REF!"?

 

[David]

Thanks, Luke. I thought the <>#VALUE! means check out for all criterias
except the NOT VALUE data. How about to include the #NUM!?

 

[Shane Devenshire]

Hi,

Try this

=SUMIF(B1:B11,"<9E9")

This handles all error types.

 

[SysMod]

or
=SUMPRODUCT(--ISERROR(A1:A100))