Summing Formulas with N/A Mixed In

D

Diana

How can I obtain a SUM of a range in which some of the
cells contain an N/A due to no match in the LOOKUP
function I used?
Example:
13
#N/A
26
10
#N/A
------
Sum returns value of #N/A but I need to find out how to
get it to return value of everything that isn't #N/A which
in this case should = 49.
Thanks so much.
Diana
 
J

JE McGimpsey

One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):

=SUM(IF(ISNA(A1:A5),"",A1:A5))
 
A

Arvi Laanemets

Hi

It'll be better to avoid errors from start. P.e. instead formula
=VLOOKUP(...)
use
=IF(ISERROR(VLOOKUP(...)),"",VLOOKUP(...))
or
=IF(ISNA(VLOOKUP(...)),"",VLOOKUP(...))

No problems with SUM() then anymore.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Scrabble Value calculation for Welsh words 0
Summing #N/A occurrences 5
match formula 2
#N/A In Cells 7
Vlookups to return a N/A value 3
How can I automatically replace the error #N/A with 0 in Excel? 3
How to set a formula if the value is with #N/A 4
summing first 'n' values in a column or row 4

Top