Sum or Avg all cells in range that are not N/A

[Cerestes]

Is there a simple formula to 'skip' cells that are "N/A" when using SUM and AVERAGE?

I need to sum (and average) across a large range, and want to ignore any cells in the range that are NA().

If the range has values of 2, 2, N/A, 2, N/A ,2 - then AVERAGE(range) = 2 and SUM(range) = 8.

Any easy solution?

 

[barry houdini]

If your range is A1:A10 you could use this for sum

=SUM(IF(ISNUMBER(A1:A10),A1:A10))

and this for average

=AVERAGE(IF(ISNUMBER(A1:A10),A1:A10))

both confirmed with CTRL+SHIFT+ENTER

 

[Brian from Maui]

Try,

=SUMIF(A1:A10,"<>#N/A")

Yeah! Edit option!