#N/A in calculations

[Guest]

Hi

I'm trying to work around some #N/A values in calculations. Columns A and B
are populated with a conditional formula, and I'm trying to sum them in
column C; so data could look like:

A1 = 3, B1 = 4 C1= sum(A1:B1)
A2 = 2, B2 = #N/A C2= sum(A2:B2)

I would like to have C2 = 2, disregarding the #N/A, but can't seem to do it.
I need to keep the #N/A in the cells in order to have zero values ignored in
some graphs built off this data.

Any hints?

TIA
JonR

 

[Guest]

Replace the #N/A values with an empty string, "", in your conditional
formulas and then column C will calculate.

Dave

 

[Guest]

Replace the #N/A values with an empty string, "", in your conditional
formulas and then column C will calculate.

Dave

 

[Bob Umlas]

in C1: ctrl/shift/enter:
=SUM(IF(ISNA(A1:B1),0,A1:B1))
then fill to C2
Bob Umlas
Excel MVP

 

[Bob Umlas]

in C1: ctrl/shift/enter:
=SUM(IF(ISNA(A1:B1),0,A1:B1))
then fill to C2
Bob Umlas
Excel MVP

 

[Guest]

Brilliant

So far so good, but I have one other case that I need to deal with:

A3= #N/A B3 = #N/A

In this case I would like C3 to also be #N/A, instead of the zero that your
formula returns. I tried putting NA() into the function where the zero is,
but then if there is an #N/A in either of the two values, then #N/A is the
result, rather than the numeric value of the one cell that is populated with
a number.

 

[Guest]

Brilliant

So far so good, but I have one other case that I need to deal with:

A3= #N/A B3 = #N/A

In this case I would like C3 to also be #N/A, instead of the zero that your
formula returns. I tried putting NA() into the function where the zero is,
but then if there is an #N/A in either of the two values, then #N/A is the
result, rather than the numeric value of the one cell that is populated with
a number.

 

[Guest]

The following formula SEEMED to work in my limited testing (and limited
understanding.) Note, the original formula seemed to need to be array
entered and as you stated it worked for you, I will assume that you already
knew that.

=IF(AND(ISNA(A1),ISNA(B1)),NA(),SUM(IF(ISNA(A1:B1),0,A1:B1)))

 

[Guest]

The following formula SEEMED to work in my limited testing (and limited
understanding.) Note, the original formula seemed to need to be array
entered and as you stated it worked for you, I will assume that you already
knew that.

=IF(AND(ISNA(A1),ISNA(B1)),NA(),SUM(IF(ISNA(A1:B1),0,A1:B1)))

 

[Guest]

That worked!

Thanks everyone!
--


JonR

The following formula SEEMED to work in my limited testing (and limited
understanding.) Note, the original formula seemed to need to be array
entered and as you stated it worked for you, I will assume that you already
knew that.

=IF(AND(ISNA(A1),ISNA(B1)),NA(),SUM(IF(ISNA(A1:B1),0,A1:B1)))

 

[Guest]

That worked!

Thanks everyone!
--


JonR

The following formula SEEMED to work in my limited testing (and limited
understanding.) Note, the original formula seemed to need to be array
entered and as you stated it worked for you, I will assume that you already
knew that.

=IF(AND(ISNA(A1),ISNA(B1)),NA(),SUM(IF(ISNA(A1:B1),0,A1:B1)))

 

[Aladin Akyurek]

C1, copied down:

=SUM(SUMIF(A1:B1,{"<0",">0"}))

 

[Aladin Akyurek]

C1, copied down:

=SUM(SUMIF(A1:B1,{"<0",">0"}))