Ignoring n/a in formulas

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Does anyone know how to ignore n/a's when using formulas. I am trying to sum up a large sum of data and it is okay that some have n/a's but need the total of the group to add up. I have used the sumif formula but that doesn't work if I have to reference another cell when computing.
 
DKS said:
Does anyone know how to ignore n/a's when using formulas. I am trying to sum
up a large sum of data and it is okay that some have n/a's but need the total
of the group to add up. I have used the sumif formula but that doesn't work
if I have to reference another cell when computing.
Click to expand...

Try,

=SUM(IF(ISNUMBER(A1:A10),A1:A10)), entered using Ctrl+Shift+Enter

and adjust your range accordingly.

Hope this helps!
 
One way

=SUMIF(A1:D200,"<>#N/A")

--

Regards,

Peo Sjoblom


DKS said:
Does anyone know how to ignore n/a's when using formulas. I am trying to
Click to expand...
sum up a large sum of data and it is okay that some have n/a's but need the
total of the group to add up. I have used the sumif formula but that
doesn't work if I have to reference another cell when computing.
 
Hi
one way: enter the following array formula (with CTRL+SHIFT+ENTER)
=SUM(IF(ISNA(A1:A100),,A1:A100))
 
=SUMIF(A:A,"<>#N/A")

HTH
Jason
Atlanta, GA
-----Original Message-----
Does anyone know how to ignore n/a's when using
Click to expand...
formulas. I am trying to sum up a large sum of data and
it is okay that some have n/a's but need the total of the
group to add up. I have used the sumif formula but that
doesn't work if I have to reference another cell when
computing.
 
Back
Top