# How do I ignore cells with #N/A in a function?

I have thousands of rows of which a number contain #N/A. Running functions such as SUM or AVERAGE report an error when selecting a row sample that includes cells with #N/A. It's time consuming to remove these lines individually. Furthermore, I prefer to keep them for transparency reasons.

Is there any way to ignore these cells?

edit retag close merge delete

Sort by » oldest newest most voted

Change your sum and average formulas to include error checking using the iserror or isna functions. Something like this will check for errors, then do your calculation:

=IF(ISNA(A5), 0, SUM(A5))


If there is an error, the result will be 0 and your further column functions will work properly.

more

Sorry! I don't understand how to add contents from "thousands of rows" this way?

Another suggestion concerning averages: {=AVERAGEIF(ISNUMBER(A7:A10333),TRUE(),A7:A10333)}

Or; {=AVERAGE(IFNA(A7:A10333;""))}

In contrary to the 0 @pierre-yves samyn (correctly) used with SUM the empty string will not spoil the counting of numbers for the average calculation. ( @karolus also used it with SUM.)

@Lupp, this is an example formula. I know summing one cell will not add many lines. The range is meant to be replaced as needed. I acknowledge that the zero may affect average functions, but it can be modified as needed.

OMG :)

Why not =SUM(IFNA(A2:A51;0)) ?

(as Matrixformula)

more

Best solution for SUM. Note though that AVERAGE (or any function where the number of elements matters) will count the 0 values as well.

=SUM(IFNA(A2:A51;"")) should do anyway since SUM and AVERAGE and similar functions ignore text.

Can I build ISNA into an OFFSET array? =OFFSET(INDIRECT($C$43);1;0;30;9) @razon_22 useless use of sum !!

something like

=SUM(IF(ISNA(A4:A12);"";A4:A12))


as Matrixformula ctrl+shift+enter

more

As I stated above, it is simply an example formula.

Another possiblity, that does not use a matrix formula is simply using "<>#N/A" as the if-condition.

=AVERAGEIF(O2:O124;"<>#N/A")
=SUMIF(O2:O124;"<>#N/A")

more