summing a row with text in some cells
 

I am trying to sum a row, but some cells have text that explain why the data
is missing. I would like any text to be summed as zeros, but I can't seem to
figure out how to make excel do that. Any information you could give me
would be appreciated.

summing a row with text in some cells
 

If you're using the SUM function it ignores text and this is essentially the
same as evaluating it as 0.

A1 = 10
A2 = XX
A3 = 10
A4 = NA
A5 = 10

=SUM(A1:A5) returns 30

--
Biff
Microsoft Excel MVP


"Jared" wrote in message
...
I am trying to sum a row, but some cells have text that explain why the
data
is missing. I would like any text to be summed as zeros, but I can't seem
to
figure out how to make excel do that. Any information you could give me
would be appreciated.

summing a row with text in some cells
 

That is assuming the na is actually just na, and not an actual error such as
#N/A.
--
John C


"T. Valko" wrote:

If you're using the SUM function it ignores text and this is essentially the
same as evaluating it as 0.

A1 = 10
A2 = XX
A3 = 10
A4 = NA
A5 = 10

=SUM(A1:A5) returns 30

--
Biff
Microsoft Excel MVP


"Jared" wrote in message
...
I am trying to sum a row, but some cells have text that explain why the
data
is missing. I would like any text to be summed as zeros, but I can't seem
to
figure out how to make excel do that. Any information you could give me
would be appreciated.

summing a row with text in some cells
 

That's true but you'll notice I typed it in as NA not as #N/A.

To exclude errors as well as text:

=SUMIF(A1:A5,"<1E100")

--
Biff
Microsoft Excel MVP


"John C" <johnc@stateofdenial wrote in message
...
That is assuming the na is actually just na, and not an actual error such
as
#N/A.
--
John C


"T. Valko" wrote:

If you're using the SUM function it ignores text and this is essentially
the
same as evaluating it as 0.

A1 = 10
A2 = XX
A3 = 10
A4 = NA
A5 = 10

=SUM(A1:A5) returns 30

--
Biff
Microsoft Excel MVP


"Jared" wrote in message
...
I am trying to sum a row, but some cells have text that explain why the
data
is missing. I would like any text to be summed as zeros, but I can't
seem
to
figure out how to make excel do that. Any information you could give
me
would be appreciated.

summing a row with text in some cells
 

True, but MS Excel help every so often refers the the #N/A error as NA. :D
--
John C


"T. Valko" wrote:

That's true but you'll notice I typed it in as NA not as #N/A.

To exclude errors as well as text:

=SUMIF(A1:A5,"<1E100")

--
Biff
Microsoft Excel MVP


"John C" <johnc@stateofdenial wrote in message
...
That is assuming the na is actually just na, and not an actual error such
as
#N/A.
--
John C


"T. Valko" wrote:

If you're using the SUM function it ignores text and this is essentially
the
same as evaluating it as 0.

A1 = 10
A2 = XX
A3 = 10
A4 = NA
A5 = 10

=SUM(A1:A5) returns 30

--
Biff
Microsoft Excel MVP


"Jared" wrote in message
...
I am trying to sum a row, but some cells have text that explain why the
data
is missing. I would like any text to be summed as zeros, but I can't
seem
to
figure out how to make excel do that. Any information you could give
me
would be appreciated.