Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
sum of cells that may contain #n/a
If I am summing cells that may or may not contain an error (#n/a) is there a
way to ignore these cells? Or replace (#n/a) with a zero? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
sum of cells that may contain #n/a
Try the below
Please note that this is an array formula. Within the cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" =SUM(IF(ISNA(A1:A10),0,A1:A10)) If this post helps click Yes --------------- Jacob Skaria "Troy" wrote: If I am summing cells that may or may not contain an error (#n/a) is there a way to ignore these cells? Or replace (#n/a) with a zero? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
sum of cells that may contain #n/a
Hi,
If all the numbers are positive you can get away with =SUMIF(A1:A20,"0") if there may be negatives in the range use =SUM(SUMIF(A1:A20,{"<0","0"})) Mike "Troy" wrote: If I am summing cells that may or may not contain an error (#n/a) is there a way to ignore these cells? Or replace (#n/a) with a zero? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
sum of cells that may contain #n/a
doesn't seem to be working. Does it matter that the cells I'm summing are
scattered all over my worksheet? "Troy" wrote: If I am summing cells that may or may not contain an error (#n/a) is there a way to ignore these cells? Or replace (#n/a) with a zero? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
sum of cells that may contain #n/a
That's not working.
I still want a sum if only one of the many cells I'm summing as a number value in it. "Mike H" wrote: Hi, If all the numbers are positive you can get away with =SUMIF(A1:A20,"0") if there may be negatives in the range use =SUM(SUMIF(A1:A20,{"<0","0"})) Mike "Troy" wrote: If I am summing cells that may or may not contain an error (#n/a) is there a way to ignore these cells? Or replace (#n/a) with a zero? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
sum of cells that may contain #n/a
Troy,
'doesn't seem to be working' isn't a very useful description of the problem, post the formula you tried. Mike "Troy" wrote: doesn't seem to be working. Does it matter that the cells I'm summing are scattered all over my worksheet? "Troy" wrote: If I am summing cells that may or may not contain an error (#n/a) is there a way to ignore these cells? Or replace (#n/a) with a zero? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
sum of cells that may contain #n/a
Sorry see below.
=SUMIF(H504,H488,H466,"0") "Mike H" wrote: Troy, 'doesn't seem to be working' isn't a very useful description of the problem, post the formula you tried. Mike "Troy" wrote: doesn't seem to be working. Does it matter that the cells I'm summing are scattered all over my worksheet? "Troy" wrote: If I am summing cells that may or may not contain an error (#n/a) is there a way to ignore these cells? Or replace (#n/a) with a zero? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
sum of cells that may contain #n/a
Maybe:
=SUM(IF(ISERROR(A1:A6),0,A1:A6)) Enter it with Ctrl+Shift+Enter all at the same time, not just Enter. Or: =SUM(IF(ISNUMBER(A1:A6),A1:A6,0)) Same as above; Ctrl+Shift+Enter HTH, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Troy" wrote: Sorry see below. =SUMIF(H504,H488,H466,"0") "Mike H" wrote: Troy, 'doesn't seem to be working' isn't a very useful description of the problem, post the formula you tried. Mike "Troy" wrote: doesn't seem to be working. Does it matter that the cells I'm summing are scattered all over my worksheet? "Troy" wrote: If I am summing cells that may or may not contain an error (#n/a) is there a way to ignore these cells? Or replace (#n/a) with a zero? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
sum of cells that may contain #n/a
Troy,
There has to be a better way but in the meantime try this array formula =SUM(IF(ISNA(H466),,H466))+SUM(IF(ISNA(H488),,H488 ))+SUM(IF(ISNA(H504),,H504)) This is an array formula which must be entered with CTRL+Shift+Enter and NOT 'just enter. If you do it correctly then Excel will put curly brackets around 'the formula{}. You can't type these yourself. If you Edit the ranges 'then you must re-enter as An array Mike "Troy" wrote: Sorry see below. =SUMIF(H504,H488,H466,"0") "Mike H" wrote: Troy, 'doesn't seem to be working' isn't a very useful description of the problem, post the formula you tried. Mike "Troy" wrote: doesn't seem to be working. Does it matter that the cells I'm summing are scattered all over my worksheet? "Troy" wrote: If I am summing cells that may or may not contain an error (#n/a) is there a way to ignore these cells? Or replace (#n/a) with a zero? |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
sum of cells that may contain #n/a
Hi
I would go for your second option: Or replace (#n/a) with a zero? In excel 2007 use an "IfError" formula in the cells to sum like this: =IfError(A1/B1,0) Which will return a the result of A1/B1 or 0 if the formula return an error. In previous versions use this: =IF(ISERROR(A1/B1),0,A1/B1) Hopes this helps. .... Per |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel: match two cells in one sheet to two cells in another and return a third cells value | Excel Worksheet Functions | |||
compare 2 column cells and return the adjacent columns cells data of the cell | Excel Worksheet Functions | |||
Select Merged Cells and Unmerge Spread Merge Data To All Cells | Excel Programming | |||
display a range of cells editible cells based on matching date | Excel Worksheet Functions | |||
Skip cells with TAB/SHIFT+TAB but allow arrow keys/mouse selection of skipped cells | Excel Programming |