SUM formula that will ignore #N/A's

[BillLillard]

Hello, I was looking for some help and Google has been a dead end. I just created a VLOOKUP table that has multiple output cells that I want to sum up. The problem is that the number of inputs will vary and I will have several #N/A returns. That's fine, as there's no value needed for those, but now I want to sum up the results, and summing up a range that includes #N/A's just spits out... an #N/A. Is there a SUM formula that will ignore the #N/A's in the range?

After all the trouble of learning how to set up the rest of this table, I can't believe I'm hung up on a simple SUM...

I found this: =SUMIF(I2:I25;"<>#N/A")
But it's not working.

Thanks,
Bill

[RusselB]

While I haven't come up with a simple solution, I can explain why the formula you posted isn't working.
The formula you posted is looking for the string #N/A in the cells.
The #N/A that you see is actually an error report, not the string #N/A (which your formula requires). It can be generated using =NA() and detected using =ISNA(<cell_reference>)
See the help file for more details.
I don't know how many or how complicated your formulas are, but one option is to check if the formula is going to generate a #N/A (using the ISNA function), and then force it to return something that isn't an error (such as your #N/A string, which in turn would make your suggested formula work).

[robleyd]

Use the function ISNA() in conjunction with your VLOOKUP() so your lookup returns a blank rather than #N/A

If (vlookup returns N/A; "";result of valid vlookup)

[Lupp]

Any expression getting an NA() operand or parameter will pass the NA() to its result if not explicitly specified to do otherwise.
The relevant function specified in this exceptional way is the logical function ISNA(AnyTypeParmeter). You can therefore use

Code: Select all

=SUM(IF(ISNA(A1:A4);"";A1:A4))

to get the sum for the range A1:A4 ignoring cells returning the NA() error. Text, also the empty text, is ignored by SUM() as you will know.

You can also use formulas like

Code: Select all

=IF(ISERROR(expression)then"";expression) or
=IF(ISNA(expression)then"";expression)

in every cell suspected to get an NA() result sometimes (by VLOOKUP() e.g.) if you accept the inefficiency of evaluating the same expression often a second time.

Since V4.0 (released 2013, week 6) LibreOffice Calc has implemented the functions IFERROR() and IFNA() usable like in the example

Code: Select all

=IFNA(expression;"")

which will return the empty string if expression evaluates to NA() and the calculated result (value or error code) otherwise. AOO did not yet implement these useful functions specifiued in OpenFormula.

[BillLillard]

Ok. Well.

Sorry, I'm not well versed in this software. I do understand that #N/A is something that one would normally want to know about and fix, but that with VLOOKUP it can be a normal part of the results. In my case, they're fine coming out of the initial calculations. All I have left is a 24 cell range that I want to sum, and these #N/A's are killing the result.

If I was to use a formula in conjuction with my VLOOKUP formulas, I wouldn't even know where to put them. I'm actually shocked that there's no way to just ignore an error. Or if there is, like with ISERROR or ISNA, I guess I just don't know how to implement it. Basically my formula would/should be =SUM(I2:I25). Very simple. Can I just add something to that to ignore the #N/A's that are in the field?

Sorry, I'm very new to this, but I appreciate the help.

[RusselB]

See the first formula supplied by Lupp

[Lupp]

I missed to make clear that that formula must be entered for array-evaluation with Ctrl+Shift+Enter. Adapted to the range given by the OQ it will then be shown for the cell as

Code: Select all

{=SUM(IF(ISNA(I2:I25);"";I2:I25))}

(The curly brackets must not be entered!)

[robleyd]

To make it fully clear, enter

Code: Select all

=SUM(IF(ISNA(I2:I25);"";I2:I25))

without the curlies in the formula bar and then press Ctrl+Shift+Enter. Calc will add the curly brackets.