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

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

Dave
--
Brevity is the soul of wit.


"JonR" wrote:

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

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

"JonR" wrote in message
...
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

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.
--
TIA
JonR


"Bob Umlas" wrote:

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

"JonR" wrote in message
...
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

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)))

--
Kevin Vaughn


"JonR" wrote:

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.
--
TIA
JonR


"Bob Umlas" wrote:

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

"JonR" wrote in message
...
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

That worked!

Thanks everyone!
--


JonR


"Kevin Vaughn" wrote:

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)))

--
Kevin Vaughn


"JonR" wrote:

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.
--
TIA
JonR


"Bob Umlas" wrote:

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

"JonR" wrote in message
...
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

C1, copied down:

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


JonR wrote:
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