G
Guest
How do I add a group of cells but ignore any cell with a value of "na"?
Thanks
Thanks
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
G
Guest
Try this:
=SUMIF(A1:A10,"<>"&"#N/A")
HTH,
Elkar
=SUMIF(A1:A10,"<>"&"#N/A")
HTH,
Elkar
D
David Biddulph
=SUM() will ignore any text values in the range.
G
Guest
Thanks...
How do I handle a situation where I want to add non-consecutive cells, such
as A1+A5+A12....
How do I handle a situation where I want to add non-consecutive cells, such
as A1+A5+A12....
G
Guest
In that case, it would probably be easier to eliminate the #N/A errors rather
than trying to SUM around them.
Do something like:
=IF(ISNA(your formula),"",your formula)
This way, if your formula results in #N/A, a blank will be returned instead.
Then, the SUM function will work on those cells. SUM(A1,A5,A12)
HTH,
Elkar
than trying to SUM around them.
Do something like:
=IF(ISNA(your formula),"",your formula)
This way, if your formula results in #N/A, a blank will be returned instead.
Then, the SUM function will work on those cells. SUM(A1,A5,A12)
HTH,
Elkar
G
Guest
Understood - but the total is adding subtotals in a sense....currently done
by +sum(a1+a4+a7) etc...
by +sum(a1+a4+a7) etc...
T
T. Valko
=SUMIF(A1:A10,"<>"&"#N/A")
Or:
=SUMIF(A1:A10,"<>#N/A")
Or:
=SUMIF(A1:A10,"<>#N/A")
P
Peo Sjoblom
=SUM(A1,A4,A7)
will ignore text but not errors
--
Regards,
Peo Sjoblom
will ignore text but not errors
--
Regards,
Peo Sjoblom
P
Peo Sjoblom
True, but not error values if the OP had #N/A from for instance a VLOOKUP
--
Regards,
Peo Sjoblom
--
Regards,
Peo Sjoblom
D
David Biddulph
Yes. I was assuming that the OP knew the difference between "na" and #N/A or NA().