| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Return Numeric Labels that have different Numeric Values | Excel Worksheet Functions | |||
| Non Numeric Values | Charts and Charting in Excel | |||
| Matching numeric values. | Excel Discussion (Misc queries) | |||
| How to average every 10 numeric values | Excel Worksheet Functions | |||
| Text values to numeric values | Excel Discussion (Misc queries) | |||
Linear Mode
