We're currently migrating from Ask to Discourse, read the details here

Ask Your Question

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

asked 2015-04-17 10:18:48 +0200

orschiro gravatar image

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 flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-10-29 20:50:21.957898

4 Answers

Sort by » oldest newest most voted

answered 2015-04-17 13:58:04 +0200

razon_22 gravatar image

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.

edit flag offensive delete link 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 gravatar imageLupp ( 2015-04-18 22:46:37 +0200 )edit

@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.

razon_22 gravatar imagerazon_22 ( 2015-04-19 01:25:18 +0200 )edit

answered 2015-04-17 17:58:28 +0200

pierre-yves samyn gravatar image

updated 2015-04-17 17:59:35 +0200

OMG :)

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

(as Matrixformula)

edit flag offensive delete link 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.

erAck gravatar imageerAck ( 2018-02-09 15:52:46 +0200 )edit

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

Lupp gravatar imageLupp ( 2018-02-09 18:14:30 +0200 )edit

Can I build ISNA into an OFFSET array? =OFFSET(INDIRECT($C$43);1;0;30;9)

pherriot gravatar imagepherriot ( 2019-06-20 10:58:25 +0200 )edit


pherriot gravatar imagepherriot ( 2019-06-20 11:43:05 +0200 )edit

answered 2015-04-17 16:45:15 +0200

karolus gravatar image

@razon_22 useless use of sum !!

something like


as Matrixformula ctrl+shift+enter

edit flag offensive delete link more


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

razon_22 gravatar imagerazon_22 ( 2015-04-19 01:29:33 +0200 )edit

answered 2018-02-09 15:15:45 +0200

vlz gravatar image

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

edit flag offensive delete link more


That does not work if the document is loaded in another UI language where the error message is different. Also, it is slower because every value has to be converted to its string representation first before comparing.

erAck gravatar imageerAck ( 2018-02-09 15:45:58 +0200 )edit

Question Tools

1 follower


Asked: 2015-04-17 10:18:48 +0200

Seen: 55,635 times

Last updated: Feb 09 '18