# Summing up spreadsheet data when a column contains “#N/A”

I am using Goggle Spreadsheet to work up some historical stock data and I use a Google function (=googlefinance=…) to import the historical closing prices for a stock, then I work with that data further. But, in that list of data generated from the =googlefinance=… function, one of the amounts comes up as #N/A. I don’t know why, but it happens for various symbols that I have tried.

When I use a max function on the array, which includes the N/A line, the max function does not come up with anything but an N/A, so the N/A throws off any further functions. I thought I’d create a second column to the right of the imported data in which I can give it an IF function, something like, If ((A1 <0), "0", A1), with the expectation that it would return 0 if cell A1 is the N/A, and the cell value if it is not N/A. However, this still returns N/A. I also tried an IS BLANK function but that resulted in the same NA.

Does anyone have any suggestions for a workaround to eliminate the N/A from an array of numbers that I am trying to work with?

• The title is about "summing up", but then you say "When I use a max function on the array …". Only one of them can be right :) Oct 16, 2018 at 17:13

Encountered this, then fiddling about I found that this works great:

=sumif(A1:A20,"<>0")


This works even if there are invalid values inside the range

• Should be =sumif(A1:A20,"<>#N/A") these days, see my answer for details. Oct 16, 2018 at 17:30

To sum up values which might contain #N/A, use this formula:

=sumif(A1:A20,"<>#N/A")


Details: It seems that in earlier versions of Google Sheets, =sumif(A1:A20,"<>0") could be used instead as in the answer by Lockzmith. Now however, Google Sheets considers #N/A<>0, so it won't work anymore, returning #N/A if any part of the sum is #N/A. Anyway, here is the new way that works nowadays.

You can use the isna(value) function to test if googlefinance returns N/A. You can use that in an if function to output whatever you want if N/A is returned.

For example:

=if(isna(googlefinance("DoesNotExist"; "price"),"NA returned",googlefinance("DoesNotExist"; "price")))


Or this example:

ColA

CCLXXX
=if(isna(A2,"Not avail",A2))


Here's a screenshot of isna working in a Google Spreadsheet: Lets say your values are in column A, and some contain N/A errors. Put this in column B:

=if(isna(A1),0,A1)


If there is any error in column A it will use 0, otherwise the value in A - then you can do max etc on column B.

• iserr didn't work with googlefinance for me, however isna did work. Did you try this yourself?
– HeatfanJohn
Nov 13, 2012 at 19:10
• iserr should work with any error, I wasn't able to test with googlefinance to make sure it matched - but isna is just as suitable.
– Paul
Nov 13, 2012 at 23:53
• The problem with iserr is that it does not consider the n/a as an error. See my answer that shows both.
– HeatfanJohn
Nov 14, 2012 at 2:00
• @HeatfanJohn Ah right. Thanks, fixed.
– Paul
Nov 14, 2012 at 3:08

=ArrayFormula(max(if(not(iserror(A:A)),A:A)))


Ran into this issue yesterday. Eventually realized that my data range that I was referencing in the original spreadsheet contained some error codes, not just numbers.

If any of the cells in the spreadsheet range you're referencing (e.g. the A1:A100 part in IMPORTRANGE("SpreadsheetURL","SheetName!A1:A100") ) contain errors such as

#NULL!
#DIV/0!
#VALUE!
#REF!
#NAME?
#NUM!
#N/A


then your formula must be modified to include SUMIF() as follows

=SUMIF(IMPORTRANGE("SpreadsheetURL","SheetName!A1:A100"), ">0")


This ensures that it's only adding actual numbers and not error codes (which will just throw another error).

• The problem with this is it assumes all positive values. If you have negative dollar values they just ignored. Mar 18, 2021 at 9:24

I needed to sum columns of which some contained both #VALUE! and #ERROR! and this is working:

=SUM(iferror(F18:F86,""))