#value! error in sum function

I need to sum a range of cells. Some of the cells may contain text. If so,
the function returns #value!. In Lotus 1-2-3, text is treated as 0 for
function purposes. Is there some way to force Excel to do the same, or
another way to get around this problem?

Instead of doing something like this:

=A1+A2+A3

Do it like this:

=SUM(A1:A3)

The SUM function ignores text.

--
Biff
Microsoft Excel MVP


"Art" wrote in message
...
I need to sum a range of cells. Some of the cells may contain text. If
so,
the function returns #value!. In Lotus 1-2-3, text is treated as 0 for
function purposes. Is there some way to force Excel to do the same, or
another way to get around this problem?

I have a very similar problem to Art.

In my case the problem arises when my 1-2-3 sheet is saved as an Excel sheet
and then I get a heap of #VALUE! results in Excel.

The reason for this is because the 1-2-3 formulas contain a lot of ""s,
which is text and can only be added by Excel using =sum, or so I understood.

Unfortunately, =sum doesn't work when several non-contiguous cells are added
and the "" cell is deducted.

Example: =SUM(A1;B1;-C1) where C1 contains "" (as a result of a condition
not having been met in C1). The =sum formula shows #VALUE! only because the
"" in C1 is deducted instead of added.

What do I have to put in my C1 formula so that, when the condition there is
not met, it shows nothing and does not cause a #VALUE! elsewhere? C1 is not
allowed to show zero because that is an incorrect value.

What is the best solution?

"T. Valko" wrote:

Instead of doing something like this:

=A1+A2+A3

Do it like this:

=SUM(A1:A3)

The SUM function ignores text.

--
Biff
Microsoft Excel MVP


"Art" wrote in message
...
I need to sum a range of cells. Some of the cells may contain text. If
so,
the function returns #value!. In Lotus 1-2-3, text is treated as 0 for
function purposes. Is there some way to force Excel to do the same, or
another way to get around this problem?

Try something like this:

=SUM(A1:B1,IF(COUNT(C1),-C1))

--
Biff
Microsoft Excel MVP


"Lancelot" wrote in message
...
I have a very similar problem to Art.

In my case the problem arises when my 1-2-3 sheet is saved as an Excel
sheet
and then I get a heap of #VALUE! results in Excel.

The reason for this is because the 1-2-3 formulas contain a lot of ""s,
which is text and can only be added by Excel using =sum, or so I
understood.

Unfortunately, =sum doesn't work when several non-contiguous cells are
added
and the "" cell is deducted.

Example: =SUM(A1;B1;-C1) where C1 contains "" (as a result of a condition
not having been met in C1). The =sum formula shows #VALUE! only because
the
"" in C1 is deducted instead of added.

What do I have to put in my C1 formula so that, when the condition there
is
not met, it shows nothing and does not cause a #VALUE! elsewhere? C1 is
not
allowed to show zero because that is an incorrect value.

What is the best solution?

"T. Valko" wrote:

Instead of doing something like this:

=A1+A2+A3

Do it like this:

=SUM(A1:A3)

The SUM function ignores text.

--
Biff
Microsoft Excel MVP


"Art" wrote in message
...
I need to sum a range of cells. Some of the cells may contain text. If
so,
the function returns #value!. In Lotus 1-2-3, text is treated as 0 for
function purposes. Is there some way to force Excel to do the same, or
another way to get around this problem?

Thank you very much T.

Your COUNT() solution solves a lot of #VALUE! cases in my spreadsheet.
However there are some cases where I have difficulty to know where to put
the COUNT(). They look like this:

=IF(OR(CK56=0;CN56=0);"";IF(CL56=0;ROUND((CK56+CN5 6)/2;0);ROUND((CK56+CL56+CN56)/3;0)))

All the mentioned cells in row 56 could contain "".

Could you help me with this case? I tried to put in one or two COUNT()s but
I couldn't get it right.

By the way, how would I design the formulas if I wrote the Excel spreadsheet
from scratch. Would I really need so many COUNT()s in the formulas?


"T. Valko" wrote:

Try something like this:

=SUM(A1:B1,IF(COUNT(C1),-C1))

--
Biff
Microsoft Excel MVP


"Lancelot" wrote in message
...
I have a very similar problem to Art.

In my case the problem arises when my 1-2-3 sheet is saved as an Excel
sheet
and then I get a heap of #VALUE! results in Excel.

The reason for this is because the 1-2-3 formulas contain a lot of ""s,
which is text and can only be added by Excel using =sum, or so I
understood.

Unfortunately, =sum doesn't work when several non-contiguous cells are
added
and the "" cell is deducted.

Example: =SUM(A1;B1;-C1) where C1 contains "" (as a result of a condition
not having been met in C1). The =sum formula shows #VALUE! only because
the
"" in C1 is deducted instead of added.

What do I have to put in my C1 formula so that, when the condition there
is
not met, it shows nothing and does not cause a #VALUE! elsewhere? C1 is
not
allowed to show zero because that is an incorrect value.

What is the best solution?

"T. Valko" wrote:

Instead of doing something like this:

=A1+A2+A3

Do it like this:

=SUM(A1:A3)

The SUM function ignores text.

--
Biff
Microsoft Excel MVP


"Art" wrote in message
...
I need to sum a range of cells. Some of the cells may contain text. If
so,
the function returns #value!. In Lotus 1-2-3, text is treated as 0 for
function purposes. Is there some way to force Excel to do the same, or
another way to get around this problem?

Thank you very much T.

With your COUNT() solution I have eliminated most of the #VALUE!s from my
worksheet. However there is one remaining which I can't get right. It looks
like this:

=IF(OR(CK56=0;CN56=0);"";IF(CL56=0;ROUND((CK56+CN5 6)/2;0);ROUND((CK56+CL56+CN56)/3;0)))

Any of the cells in row 56 can and do contain "".

I tried to insert some COUNT()s in the formula, but it was not successful.
Could you spare a moment to help me with this one?

By the way, I wonder how I would formulate the Excel worksheet if I was
designing it from scratch. Would I really need to have all these COUNT()s in
the formulas - what do other users do?



"T. Valko" wrote:

Try something like this:

=SUM(A1:B1,IF(COUNT(C1),-C1))

--
Biff
Microsoft Excel MVP


"Lancelot" wrote in message
...
I have a very similar problem to Art.

In my case the problem arises when my 1-2-3 sheet is saved as an Excel
sheet
and then I get a heap of #VALUE! results in Excel.

The reason for this is because the 1-2-3 formulas contain a lot of ""s,
which is text and can only be added by Excel using =sum, or so I
understood.

Unfortunately, =sum doesn't work when several non-contiguous cells are
added
and the "" cell is deducted.

Example: =SUM(A1;B1;-C1) where C1 contains "" (as a result of a condition
not having been met in C1). The =sum formula shows #VALUE! only because
the
"" in C1 is deducted instead of added.

What do I have to put in my C1 formula so that, when the condition there
is
not met, it shows nothing and does not cause a #VALUE! elsewhere? C1 is
not
allowed to show zero because that is an incorrect value.

What is the best solution?

"T. Valko" wrote:

Instead of doing something like this:

=A1+A2+A3

Do it like this:

=SUM(A1:A3)

The SUM function ignores text.

--
Biff
Microsoft Excel MVP


"Art" wrote in message
...
I need to sum a range of cells. Some of the cells may contain text. If
so,
the function returns #value!. In Lotus 1-2-3, text is treated as 0 for
function purposes. Is there some way to force Excel to do the same, or
another way to get around this problem?

Here's how you end up getting a #VALUE! error...

A1 = a formula that might return either a number or a formula blank "".
A2 = same thing

If either A1 or A2 contain the formula blank then:

=A1+A2 will return #VALUE!

Excel is telling you that you can't add TEXT values to numeric values.

In these cases use the SUM function. SUM will ignore any text entries which
is what a formula blank is.

=SUM(A1:A2)

So, with this formula:

=IF(OR(CK56=0;CN56=0);"";IF(CL56=0;ROUND((CK56+CN 56)/2;0);ROUND((CK56+CL56+CN56)/3;0)))

Try it like this:

=IF(OR(CK56=0;CN56=0);"";IF(CL56=0;ROUND(SUM(CK56; CN56)/2;0);ROUND(SUM(CK56:CL56;CN56)/3;0)))

--
Biff
Microsoft Excel MVP


"Lancelot" wrote in message
...
Thank you very much T.

Your COUNT() solution solves a lot of #VALUE! cases in my spreadsheet.
However there are some cases where I have difficulty to know where to put
the COUNT(). They look like this:

=IF(OR(CK56=0;CN56=0);"";IF(CL56=0;ROUND((CK56+CN5 6)/2;0);ROUND((CK56+CL56+CN56)/3;0)))

All the mentioned cells in row 56 could contain "".

Could you help me with this case? I tried to put in one or two COUNT()s
but
I couldn't get it right.

By the way, how would I design the formulas if I wrote the Excel
spreadsheet
from scratch. Would I really need so many COUNT()s in the formulas?


"T. Valko" wrote:

Try something like this:

=SUM(A1:B1,IF(COUNT(C1),-C1))

--
Biff
Microsoft Excel MVP


"Lancelot" wrote in message
...
I have a very similar problem to Art.

In my case the problem arises when my 1-2-3 sheet is saved as an Excel
sheet
and then I get a heap of #VALUE! results in Excel.

The reason for this is because the 1-2-3 formulas contain a lot of ""s,
which is text and can only be added by Excel using =sum, or so I
understood.

Unfortunately, =sum doesn't work when several non-contiguous cells are
added
and the "" cell is deducted.

Example: =SUM(A1;B1;-C1) where C1 contains "" (as a result of a
condition
not having been met in C1). The =sum formula shows #VALUE! only because
the
"" in C1 is deducted instead of added.

What do I have to put in my C1 formula so that, when the condition
there
is
not met, it shows nothing and does not cause a #VALUE! elsewhere? C1 is
not
allowed to show zero because that is an incorrect value.

What is the best solution?

"T. Valko" wrote:

Instead of doing something like this:

=A1+A2+A3

Do it like this:

=SUM(A1:A3)

The SUM function ignores text.

--
Biff
Microsoft Excel MVP


"Art" wrote in message
...
I need to sum a range of cells. Some of the cells may contain text.
If
so,
the function returns #value!. In Lotus 1-2-3, text is treated as 0
for
function purposes. Is there some way to force Excel to do the same,
or
another way to get around this problem?

Thank you T.

Your formula solved my problem.
I only had to adapt it, so that where there is a condition with a value 0, I
had to change the 0 to "" in some cases, because the conditioning cell may
contain "".

P.S. Ignore my duplicated posting of the 10/15/2009 - there was a connection
problem on that day and I don't know how to remove or change my repeat
posting.

Thanks again,
Lancelot

"T. Valko" wrote:

Here's how you end up getting a #VALUE! error...

A1 = a formula that might return either a number or a formula blank "".
A2 = same thing

If either A1 or A2 contain the formula blank then:

=A1+A2 will return #VALUE!

Excel is telling you that you can't add TEXT values to numeric values.

In these cases use the SUM function. SUM will ignore any text entries which
is what a formula blank is.

=SUM(A1:A2)

So, with this formula:

=IF(OR(CK56=0;CN56=0);"";IF(CL56=0;ROUND((CK56+CN 56)/2;0);ROUND((CK56+CL56+CN56)/3;0)))

Try it like this:

=IF(OR(CK56=0;CN56=0);"";IF(CL56=0;ROUND(SUM(CK56; CN56)/2;0);ROUND(SUM(CK56:CL56;CN56)/3;0)))

--
Biff
Microsoft Excel MVP


"Lancelot" wrote in message
...
Thank you very much T.

Your COUNT() solution solves a lot of #VALUE! cases in my spreadsheet.
However there are some cases where I have difficulty to know where to put
the COUNT(). They look like this:

=IF(OR(CK56=0;CN56=0);"";IF(CL56=0;ROUND((CK56+CN5 6)/2;0);ROUND((CK56+CL56+CN56)/3;0)))

All the mentioned cells in row 56 could contain "".

Could you help me with this case? I tried to put in one or two COUNT()s
but
I couldn't get it right.

By the way, how would I design the formulas if I wrote the Excel
spreadsheet
from scratch. Would I really need so many COUNT()s in the formulas?


"T. Valko" wrote:

Try something like this:

=SUM(A1:B1,IF(COUNT(C1),-C1))

--
Biff
Microsoft Excel MVP


"Lancelot" wrote in message
...
I have a very similar problem to Art.

In my case the problem arises when my 1-2-3 sheet is saved as an Excel
sheet
and then I get a heap of #VALUE! results in Excel.

The reason for this is because the 1-2-3 formulas contain a lot of ""s,
which is text and can only be added by Excel using =sum, or so I
understood.

Unfortunately, =sum doesn't work when several non-contiguous cells are
added
and the "" cell is deducted.

Example: =SUM(A1;B1;-C1) where C1 contains "" (as a result of a
condition
not having been met in C1). The =sum formula shows #VALUE! only because
the
"" in C1 is deducted instead of added.

What do I have to put in my C1 formula so that, when the condition
there
is
not met, it shows nothing and does not cause a #VALUE! elsewhere? C1 is
not
allowed to show zero because that is an incorrect value.

What is the best solution?

"T. Valko" wrote:

Instead of doing something like this:

=A1+A2+A3

Do it like this:

=SUM(A1:A3)

The SUM function ignores text.

--
Biff
Microsoft Excel MVP


"Art" wrote in message
...
I need to sum a range of cells. Some of the cells may contain text.
If
so,
the function returns #value!. In Lotus 1-2-3, text is treated as 0
for
function purposes. Is there some way to force Excel to do the same,
or
another way to get around this problem?








.

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Lancelot" wrote in message
...
Thank you T.

Your formula solved my problem.
I only had to adapt it, so that where there is a condition with a value 0,
I
had to change the 0 to "" in some cases, because the conditioning cell may
contain "".

P.S. Ignore my duplicated posting of the 10/15/2009 - there was a
connection
problem on that day and I don't know how to remove or change my repeat
posting.

Thanks again,
Lancelot

"T. Valko" wrote:

Here's how you end up getting a #VALUE! error...

A1 = a formula that might return either a number or a formula blank "".
A2 = same thing

If either A1 or A2 contain the formula blank then:

=A1+A2 will return #VALUE!

Excel is telling you that you can't add TEXT values to numeric values.

In these cases use the SUM function. SUM will ignore any text entries
which
is what a formula blank is.

=SUM(A1:A2)

So, with this formula:

=IF(OR(CK56=0;CN56=0);"";IF(CL56=0;ROUND((CK56+CN 56)/2;0);ROUND((CK56+CL56+CN56)/3;0)))

Try it like this:

=IF(OR(CK56=0;CN56=0);"";IF(CL56=0;ROUND(SUM(CK56; CN56)/2;0);ROUND(SUM(CK56:CL56;CN56)/3;0)))

--
Biff
Microsoft Excel MVP


"Lancelot" wrote in message
...
Thank you very much T.

Your COUNT() solution solves a lot of #VALUE! cases in my spreadsheet.
However there are some cases where I have difficulty to know where to
put
the COUNT(). They look like this:

=IF(OR(CK56=0;CN56=0);"";IF(CL56=0;ROUND((CK56+CN5 6)/2;0);ROUND((CK56+CL56+CN56)/3;0)))

All the mentioned cells in row 56 could contain "".

Could you help me with this case? I tried to put in one or two COUNT()s
but
I couldn't get it right.

By the way, how would I design the formulas if I wrote the Excel
spreadsheet
from scratch. Would I really need so many COUNT()s in the formulas?


"T. Valko" wrote:

Try something like this:

=SUM(A1:B1,IF(COUNT(C1),-C1))

--
Biff
Microsoft Excel MVP


"Lancelot" wrote in message
...
I have a very similar problem to Art.

In my case the problem arises when my 1-2-3 sheet is saved as an
Excel
sheet
and then I get a heap of #VALUE! results in Excel.

The reason for this is because the 1-2-3 formulas contain a lot of
""s,
which is text and can only be added by Excel using =sum, or so I
understood.

Unfortunately, =sum doesn't work when several non-contiguous cells
are
added
and the "" cell is deducted.

Example: =SUM(A1;B1;-C1) where C1 contains "" (as a result of a
condition
not having been met in C1). The =sum formula shows #VALUE! only
because
the
"" in C1 is deducted instead of added.

What do I have to put in my C1 formula so that, when the condition
there
is
not met, it shows nothing and does not cause a #VALUE! elsewhere? C1
is
not
allowed to show zero because that is an incorrect value.

What is the best solution?

"T. Valko" wrote:

Instead of doing something like this:

=A1+A2+A3

Do it like this:

=SUM(A1:A3)

The SUM function ignores text.

--
Biff
Microsoft Excel MVP


"Art" wrote in message
...
I need to sum a range of cells. Some of the cells may contain
text.
If
so,
the function returns #value!. In Lotus 1-2-3, text is treated as
0
for
function purposes. Is there some way to force Excel to do the
same,
or
another way to get around this problem?








.