Summing Formulas with N/A Mixed In

  • Thread starter Thread starter Diana
  • Start date Start date
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
 
One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):

=SUM(IF(ISNA(A1:A5),"",A1:A5))
 
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.
 
Back
Top