getting rid of "#N/A" in columns

[Guest]

How do i get rid of the dreaded #N/A answer? I have a cell that is a sum of
6 cells, but those 6 cells are also sums of other cells...EX:

Group 1 fills out A1, A2 & A3...the fromula in A4 automatically sums those.
Group 2 fills out B1, B2, & B3...the formula in B4 automatically sums those.
Group 3 fills out C1, C2, & C3...the formula in C4 automatically sums those.

I want a sum of all of those, so in A10 I say =A4+B4+C4...however there
maybe some times when column C isn't filled in so there is an #N/A in C4, so
my sum in A10 will read #N/A instead of summing the two of three that are
filled in..any suggestions?

 

[aidan.heritage]

How do i get rid of the dreaded #N/A answer? I have a cell that is a sum of
6 cells, but those 6 cells are also sums of other cells...EX:

Group 1 fills out A1, A2 & A3...the fromula in A4 automatically sums those.
Group 2 fills out B1, B2, & B3...the formula in B4 automatically sums those.
Group 3 fills out C1, C2, & C3...the formula in C4 automatically sums those.

I want a sum of all of those, so in A10 I say =A4+B4+C4...however there
maybe some times when column C isn't filled in so there is an #N/A in C4, so
my sum in A10 will read #N/A instead of summing the two of three that are
filled in..any suggestions?

I would PERSONALLY make the formula in row 4 return a blank instead of
an error, but you could use a SUMIF statement (for example,
sumif(range,">0") - this would discount the errors

 

[Guest]

=SUM(IF(ISNUMBER(A44),A44))

ctrl+shift+enter, not just enter

 

[Guest]

Try this
On each of the sum formulas:

If(Iserror(Sum(A1:A5)),0,Sum(A1:A5))

 

[Guest]

=SUM(IF(ISNUMBER(A44),A44))

enter with Ctrl+Shift+Enter

 

[Guest]

Toppers,
Thanks again for your quick reply, however none of the above answers worked...
It may be because the cells i'm trying to add are spread through out the
spread sheet...as an example, one of my formulas is as follows:
=(B20+D20+G20+B41+D41+G41)...but G20 is the #N/A (because there is a formula
in G20), and B20 & D20 are numbers...so how do i tell it to add those 6 so
long as there is a number in there? Any further suggestions?

 

[Peo Sjoblom]

=SUMPRODUCT(SUMIF(INDIRECT({"B20","D20","G20","B41","D41","G41"}),"<=0"&999^99))

will work

however it is insane using a formula like this when all you have to do is to
fix the formula(s) with the #N/A in the first place.

=IF(ISNA(Formula),0,Formula)

or

=IF(ISNA(Formula),"",Formula)

then use


=SUM(B20,D20,G20,B41,D41,G41)


The formula I provided is volatile and will slow down the workbook if it has
lots of formulas and it will always prompt you to save the workbook
regardless whether you have changed anything or not



--
Regards,

Peo Sjoblom

 

[Guest]

Peo,
The first formula that you gave, worked!! Thanks for the reply!!!

 

[Harlan Grove]

=SUMPRODUCT(SUMIF(INDIRECT({"B20","D20","G20","B41","D41","G41"}),
"<=0"&999^99))

will work

In addition to being volatile, it's also rather inflexible if at some
later date the OP wants to move any of these cells, insert/delete rows/
columns, etc. Since this approach already involves listing each cell
separately, a more flexible, nonvolatile alternative would be

=SUMIF(B20,"<=9.9999999999999E307")+SUMIF(D20,"<=9.9999999999999E307")
+SUMIF(G20,"<=9.9999999999999E307")+SUMIF(B41,"<=9.9999999999999E307")
+SUMIF(D41,"<=9.9999999999999E307")+SUMIF(G41,"<=9.9999999999999E307")

or define the name NUMS referring to ="<=9.99999999999999E307" and
change the formula to

=SUMIF(B20,NUMS)+SUMIF(D20,NUMS)+SUMIF(G20,NUMS)+SUMIF(B41,NUMS)
+SUMIF(D41,NUMS)+SUMIF(G41,NUMS)

however it is insane using a formula like this when all you have
to do is to fix the formula(s) with the #N/A in the first place.

....

Still quite true. Handling errors at the source is much easier in the
long run than handling them down stream.

 

[T. Valko]

Another one:

Array** entered:

=SUM(IF(ISNUMBER(CHOOSE({1,2,3,4,5,6},B20,D20,G20,B41,D41,G41)),CHOOSE({1,2,3,4,5,6},B20,D20,G20,B41,D41,G41)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)