ExcelBanter - Sum function

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Sum function (https://www.excelbanter.com/excel-worksheet-functions/89754-sum-function.html)

I have a spreadsheet in which the sum function returns a zero value from a
range with positive numbers. The sum formula is correctly entered. Any ideas?

Thanks

"Stephen Connell" wrote in
message ...
I have a spreadsheet in which the sum function returns a zero value from a
range with positive numbers. The sum formula is correctly entered. Any
ideas?

The usual explanation is that the "numbers" aren't actually numbers, but
cells formatted as text.
--
David Biddulph

It sounds like the "numbers" are really numeric text.

Try this:
Select the range to be summed
<data<text-to-columns....Click the [Finish] button

That should convert any numeric text to actual numbers. Does the summary
cell show a non-zero total now?
***********
Regards,
Ron

XL2002, WinXP

"Stephen Connell" wrote:

I have a spreadsheet in which the sum function returns a zero value from a
range with positive numbers. The sum formula is correctly entered. Any ideas?

Thanks

Right click a new, unused cell, and choose "Copy".
Select the "bad" numbers.
Right click in that selection and choose "Paste Special".
Click on "Add", then <OK.

This should transform all 'text' numbered cells to *real*, XL recognized
numbers.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"ck in the sStephen Connell"
wrote in message ...
I have a spreadsheet in which the sum function returns a zero value from a
range with positive numbers. The sum formula is correctly entered. Any
ideas?

Thanks

TTC will *only* correct numbers designated as 'text' by the use of a
prefixed apostrophe.
If cells were formatted as 'text' prior to the entry of the numbers, TTC
won't work on those cells.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Ron Coderre" wrote in message
...
It sounds like the "numbers" are really numeric text.

Try this:
Select the range to be summed
<data<text-to-columns....Click the [Finish] button

That should convert any numeric text to actual numbers. Does the summary
cell show a non-zero total now?
***********
Regards,
Ron

XL2002, WinXP

"Stephen Connell" wrote:

I have a spreadsheet in which the sum function returns a zero value from
a
range with positive numbers. The sum formula is correctly entered. Any
ideas?

Thanks

I stand corrected.
TTC does allow the Sum() function to calculate in those 'text' formatted
cells, even though the cell remains formatted as 'text'.

Sorry!
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Ragdyer" wrote in message
...
TTC will *only* correct numbers designated as 'text' by the use of a
prefixed apostrophe.
If cells were formatted as 'text' prior to the entry of the numbers, TTC
won't work on those cells.
--
Regards,

RD

--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit !
--------------------------------------------------------------------------
-
"Ron Coderre" wrote in message
...
It sounds like the "numbers" are really numeric text.

Try this:
Select the range to be summed
<data<text-to-columns....Click the [Finish] button

That should convert any numeric text to actual numbers. Does the summary
cell show a non-zero total now?
***********
Regards,
Ron

XL2002, WinXP

"Stephen Connell" wrote:

I have a spreadsheet in which the sum function returns a zero value
from
a
range with positive numbers. The sum formula is correctly entered. Any
ideas?

Thanks

Thanks, RD....(I was wondering why TTC was working ok on *my* PC. )

But, I think your suggestion of Copy/Paste_Special.Add is the better way to
go.

***********
Best Regards,
Ron

XL2002, WinXP

"Ragdyer" wrote:

I stand corrected.
TTC does allow the Sum() function to calculate in those 'text' formatted
cells, even though the cell remains formatted as 'text'.

Sorry!
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Ragdyer" wrote in message
...
TTC will *only* correct numbers designated as 'text' by the use of a
prefixed apostrophe.
If cells were formatted as 'text' prior to the entry of the numbers, TTC
won't work on those cells.
--
Regards,

RD

--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit !
--------------------------------------------------------------------------
-
"Ron Coderre" wrote in message
...
It sounds like the "numbers" are really numeric text.

Try this:
Select the range to be summed
<data<text-to-columns....Click the [Finish] button

That should convert any numeric text to actual numbers. Does the summary
cell show a non-zero total now?
***********
Regards,
Ron

XL2002, WinXP

"Stephen Connell" wrote:

I have a spreadsheet in which the sum function returns a zero value
from
a
range with positive numbers. The sum formula is correctly entered. Any
ideas?

Thanks

I prefer TTC *if* the data is in a single column.

The "Paste Special", of course, is the better way to go for global or
multi-column data revisions.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Ron Coderre" wrote in message
...
Thanks, RD....(I was wondering why TTC was working ok on *my* PC. )

But, I think your suggestion of Copy/Paste_Special.Add is the better way
to
go.

***********
Best Regards,
Ron

XL2002, WinXP

"Ragdyer" wrote:

I stand corrected.
TTC does allow the Sum() function to calculate in those 'text' formatted
cells, even though the cell remains formatted as 'text'.

Sorry!
--
Regards,

RD

--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit
!

--------------------------------------------------------------------------
-
"Ragdyer" wrote in message
...
TTC will *only* correct numbers designated as 'text' by the use of a
prefixed apostrophe.
If cells were formatted as 'text' prior to the entry of the numbers,
TTC
won't work on those cells.
--
Regards,

RD

--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may
benefit !

--------------------------------------------------------------------------
-
"Ron Coderre" wrote in message
...
It sounds like the "numbers" are really numeric text.

Try this:
Select the range to be summed
<data<text-to-columns....Click the [Finish] button

That should convert any numeric text to actual numbers. Does the
summary
cell show a non-zero total now?
***********
Regards,
Ron

XL2002, WinXP

"Stephen Connell" wrote:

I have a spreadsheet in which the sum function returns a zero
value
from
a
range with positive numbers. The sum formula is correctly entered.
Any
ideas?

Thanks

To add some more confusion <g

The copy an empty cell/paste special method:

If the cell being copied is formatted as TEXT and after the paste
specialadd, the SUM will return the correct result but the value returned
still "appears" to be TEXT (cell is aligned left). However, is you test
that value: =ISNUMBER( ) returns TRUE, =ISTEXT( ) returns FALSE. If you
check the format of the SUM cell is will show as TEXT.

Isn't this stuff loads 'o fun?

Biff

"Ragdyer" wrote in message
...
I prefer TTC *if* the data is in a single column.

The "Paste Special", of course, is the better way to go for global or
multi-column data revisions.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Ron Coderre" wrote in message
...
Thanks, RD....(I was wondering why TTC was working ok on *my* PC. )

But, I think your suggestion of Copy/Paste_Special.Add is the better way
to
go.

***********
Best Regards,
Ron

XL2002, WinXP

"Ragdyer" wrote:

I stand corrected.
TTC does allow the Sum() function to calculate in those 'text'
formatted
cells, even though the cell remains formatted as 'text'.

Sorry!
--
Regards,

RD

--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit
!

--------------------------------------------------------------------------
-
"Ragdyer" wrote in message
...
TTC will *only* correct numbers designated as 'text' by the use of a
prefixed apostrophe.
If cells were formatted as 'text' prior to the entry of the numbers,
TTC
won't work on those cells.
--
Regards,

RD

--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may
benefit !

--------------------------------------------------------------------------
-
"Ron Coderre" wrote in message
...
It sounds like the "numbers" are really numeric text.

Try this:
Select the range to be summed
<data<text-to-columns....Click the [Finish] button

That should convert any numeric text to actual numbers. Does the
summary
cell show a non-zero total now?
***********
Regards,
Ron

XL2002, WinXP

"Stephen Connell" wrote:

I have a spreadsheet in which the sum function returns a zero
value
from
a
range with positive numbers. The sum formula is correctly
entered.
Any
ideas?

Thanks

Of course I'm nit-picking <bg, BUT ...
I, and "most" of what I've read in these groups, DO stipulate:
"new, unused cell"
Which of course means that the *unused* cell is formatted to the XL default
'General' format.

This does mean however, that the OP will *always* know which cells are/have
been, unused.
That is, of course, it they even noticed the 'unused' stipulation in the
instructions in the first place.<g
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Biff" wrote in message
...
To add some more confusion <g

The copy an empty cell/paste special method:

If the cell being copied is formatted as TEXT and after the paste
specialadd, the SUM will return the correct result but the value returned
still "appears" to be TEXT (cell is aligned left). However, is you test
that value: =ISNUMBER( ) returns TRUE, =ISTEXT( ) returns FALSE. If you
check the format of the SUM cell is will show as TEXT.

Isn't this stuff loads 'o fun?

Biff

"Ragdyer" wrote in message
...
I prefer TTC *if* the data is in a single column.

The "Paste Special", of course, is the better way to go for global or
multi-column data revisions.
--
Regards,

RD

--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit
!

--------------------------------------------------------------------------
-
"Ron Coderre" wrote in message
...
Thanks, RD....(I was wondering why TTC was working ok on *my* PC. )

But, I think your suggestion of Copy/Paste_Special.Add is the better
way
to
go.

***********
Best Regards,
Ron

XL2002, WinXP

"Ragdyer" wrote:

I stand corrected.
TTC does allow the Sum() function to calculate in those 'text'
formatted
cells, even though the cell remains formatted as 'text'.

Sorry!
--
Regards,

RD

-------------------------------------------------------------------------
-
-
Please keep all correspondence within the NewsGroup, so all may
benefit
!

-------------------------------------------------------------------------
-
-
"Ragdyer" wrote in message
...
TTC will *only* correct numbers designated as 'text' by the use of
a
prefixed apostrophe.
If cells were formatted as 'text' prior to the entry of the
numbers,
TTC
won't work on those cells.
--
Regards,

RD

-------------------------------------------------------------------------
-
-
Please keep all correspondence within the NewsGroup, so all may
benefit !

-------------------------------------------------------------------------
-
-
"Ron Coderre" wrote in message
...
It sounds like the "numbers" are really numeric text.

Try this:
Select the range to be summed
<data<text-to-columns....Click the [Finish] button

That should convert any numeric text to actual numbers. Does the
summary
cell show a non-zero total now?
***********
Regards,
Ron

XL2002, WinXP

"Stephen Connell" wrote:

I have a spreadsheet in which the sum function returns a zero
value
from
a
range with positive numbers. The sum formula is correctly
entered.
Any
ideas?

Thanks

Another way is to enter this array* formula instead of your normal sum(
) formula:

=SUM(VALUE(range of cells))

*As this is an array formula, once you have typed it in (or
subsequently edit it) you must use CTRL-SHIFT-ENTER instead of the
usual ENTER. If you do this correctly then Excel will add curly braces
{ } around the formula - you must not type these yourself.

Though changing the text to numbers as described above is the better
way if you want to use the numbers in other calculations, this gives
you an alternative.

Hope this helps.

Pete