Sum the column with the error cells
 

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

Sum the column with the error cells
 

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!"?

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"David" wrote:

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

Sum the column with the error cells
 

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

"Luke M" wrote:

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!"?

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"David" wrote:

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

Sum the column with the error cells
 

Hi,

Try this

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

This handles all error types.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"David" wrote:

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

"Luke M" wrote:

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!"?

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"David" wrote:

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

Sum the column with the error cells
 

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