# 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 :) – tanius Oct 16 '18 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. – tanius Oct 16 '18 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 '12 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 '12 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 '12 at 2:00
• @HeatfanJohn Ah right. Thanks, fixed. – Paul Nov 14 '12 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).