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?
OMG 
Why not =SUM(IFNA(A2:A51;0)) ?
(as Matrixformula)
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)
oops…IFNA
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.
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 @PYS (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.
@razon_22 useless use of sum !!
something like
=SUM(IF(ISNA(A4:A12);"";A4:A12))
as Matrixformula ctrl+shift+enter
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")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.