How to limit sum function to numeric values
 

How do I add a group of cells but ignore any cell with a value of "na"?

Thanks

How to limit sum function to numeric values
 

Try this:

=SUMIF(A1:A10,"<"&"#N/A")

HTH,
Elkar


"jmcclain" wrote:

How do I add a group of cells but ignore any cell with a value of "na"?

Thanks

How to limit sum function to numeric values
 

=SUM() will ignore any text values in the range.
--
David Biddulph

"jmcclain" wrote in message
...
How do I add a group of cells but ignore any cell with a value of "na"?

Thanks

How to limit sum function to numeric values
 

Thanks...

How do I handle a situation where I want to add non-consecutive cells, such
as A1+A5+A12....

"Elkar" wrote:

Try this:

=SUMIF(A1:A10,"<"&"#N/A")

HTH,
Elkar


"jmcclain" wrote:

How do I add a group of cells but ignore any cell with a value of "na"?

Thanks

How to limit sum function to numeric values
 

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


"jmcclain" wrote:

Thanks...

How do I handle a situation where I want to add non-consecutive cells, such
as A1+A5+A12....

"Elkar" wrote:

Try this:

=SUMIF(A1:A10,"<"&"#N/A")

HTH,
Elkar


"jmcclain" wrote:

How do I add a group of cells but ignore any cell with a value of "na"?

Thanks

How to limit sum function to numeric values
 

Understood - but the total is adding subtotals in a sense....currently done
by +sum(a1+a4+a7) etc...

"David Biddulph" wrote:

=SUM() will ignore any text values in the range.
--
David Biddulph

"jmcclain" wrote in message
...
How do I add a group of cells but ignore any cell with a value of "na"?

Thanks

How to limit sum function to numeric values
 

=SUMIF(A1:A10,"<"&"#N/A")

Or:

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



--
Biff
Microsoft Excel MVP


"Elkar" wrote in message
...
Try this:

=SUMIF(A1:A10,"<"&"#N/A")

HTH,
Elkar


"jmcclain" wrote:

How do I add a group of cells but ignore any cell with a value of "na"?

Thanks

How to limit sum function to numeric values
 

=SUM(A1,A4,A7)

will ignore text but not errors


--


Regards,


Peo Sjoblom



"jmcclain" wrote in message
...
Understood - but the total is adding subtotals in a sense....currently
done
by +sum(a1+a4+a7) etc...

"David Biddulph" wrote:

=SUM() will ignore any text values in the range.
--
David Biddulph

"jmcclain" wrote in message
...
How do I add a group of cells but ignore any cell with a value of "na"?

Thanks

How to limit sum function to numeric values
 

True, but not error values if the OP had #N/A from for instance a VLOOKUP


--


Regards,


Peo Sjoblom


"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
=SUM() will ignore any text values in the range.
--
David Biddulph

"jmcclain" wrote in message
...
How do I add a group of cells but ignore any cell with a value of "na"?

Thanks

How to limit sum function to numeric values
 

Yes. I was assuming that the OP knew the difference between "na" and #N/A or NA().
--
David Biddulph

"Peo Sjoblom" wrote in message ...
True, but not error values if the OP had #N/A from for instance a VLOOKUP

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
=SUM() will ignore any text values in the range.
--
David Biddulph

"jmcclain" wrote in message
...
How do I add a group of cells but ignore any cell with a value of "na"?

Thanks