Can't sum a series of numbers
 

I copied two sets of numbers based on two different queries from the same
source into a spreadhseet. The first set of numbers I copied into the
spreadsheet will sum up fine. The second set of numbers will not sum. I
tried reformatting the cells as number with 2 decimal places and then when I
highlight the cells the bottom right tally shows a count not a sum as I'd
expect. There are no $ signs, no commas, etc. and I have already tried
reformatting the cells in various different number formats (number with 2
decimals, currency, etc.) and that doesn't help. If I manually type over the
figure in the cell it will then be included in the sum total (without
changing any formatting). This is a very large spreadsheet and it is not
practical to retype everything. Is there a way to reformat the cells which
I've not tried that might work? Any other suggestions to get them to total
up?

Thanks in advance for any help you can render.

Can't sum a series of numbers
 

Right click in a new, empty, unused cell, and choose "Copy".

Select all the "bad" cells that won't sum.
Right click in that selection and choose "Paste Special".
Click on "Add",
then <OK,
then <Esc.

And you should now have XL recognizable, summable, numbers.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Cathy" wrote in message
...
I copied two sets of numbers based on two different queries from the same
source into a spreadhseet. The first set of numbers I copied into the
spreadsheet will sum up fine. The second set of numbers will not sum. I
tried reformatting the cells as number with 2 decimal places and then when
I
highlight the cells the bottom right tally shows a count not a sum as I'd
expect. There are no $ signs, no commas, etc. and I have already tried
reformatting the cells in various different number formats (number with 2
decimals, currency, etc.) and that doesn't help. If I manually type over
the
figure in the cell it will then be included in the sum total (without
changing any formatting). This is a very large spreadsheet and it is not
practical to retype everything. Is there a way to reformat the cells
which
I've not tried that might work? Any other suggestions to get them to
total
up?

Thanks in advance for any help you can render.

Can't sum a series of numbers
 

Try this to convert it all at one go to real numbers
Enter in any empty cell: 1, then copy that cell
Select the "second set of numbers", click Edit Paste special Multiply OK
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,200 Files:354 Subscribers:53
xdemechanik
---
"Cathy" wrote:
I copied two sets of numbers based on two different queries from the same
source into a spreadhseet. The first set of numbers I copied into the
spreadsheet will sum up fine. The second set of numbers will not sum. I
tried reformatting the cells as number with 2 decimal places and then when I
highlight the cells the bottom right tally shows a count not a sum as I'd
expect. There are no $ signs, no commas, etc. and I have already tried
reformatting the cells in various different number formats (number with 2
decimals, currency, etc.) and that doesn't help. If I manually type over the
figure in the cell it will then be included in the sum total (without
changing any formatting). This is a very large spreadsheet and it is not
practical to retype everything. Is there a way to reformat the cells which
I've not tried that might work? Any other suggestions to get them to total
up?

Thanks in advance for any help you can render.

Can't sum a series of numbers
 

Max, FWIW
One point about the "multiply" option as opposed to the "add" option.

If some of the cells are not contiguous, with the "add" option, you could
select an all encompassing block of cells, with an easy single selection,
that could go beyond the cells in question, and include empty and/or blank
surrounding cells.

If you tried this with the "multiply" option, those empty cells would become
filled with 0's.

--
Regards,

RD

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

"Max" wrote in message
...
Try this to convert it all at one go to real numbers
Enter in any empty cell: 1, then copy that cell
Select the "second set of numbers", click Edit Paste special Multiply
OK
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,200 Files:354 Subscribers:53
xdemechanik
---
"Cathy" wrote:
I copied two sets of numbers based on two different queries from the same
source into a spreadhseet. The first set of numbers I copied into the
spreadsheet will sum up fine. The second set of numbers will not sum. I
tried reformatting the cells as number with 2 decimal places and then
when I
highlight the cells the bottom right tally shows a count not a sum as I'd
expect. There are no $ signs, no commas, etc. and I have already tried
reformatting the cells in various different number formats (number with 2
decimals, currency, etc.) and that doesn't help. If I manually type over
the
figure in the cell it will then be included in the sum total (without
changing any formatting). This is a very large spreadsheet and it is not
practical to retype everything. Is there a way to reformat the cells
which
I've not tried that might work? Any other suggestions to get them to
total
up?

Thanks in advance for any help you can render.

Can't sum a series of numbers
 

Thanks for the repsonse. I tried both the multiply and the add special paste
and neither worked. I'm still manually keying data to get it to work but
would really appreciate anything else to try to get the sum to work.

"RagDyer" wrote:

Max, FWIW
One point about the "multiply" option as opposed to the "add" option.

If some of the cells are not contiguous, with the "add" option, you could
select an all encompassing block of cells, with an easy single selection,
that could go beyond the cells in question, and include empty and/or blank
surrounding cells.

If you tried this with the "multiply" option, those empty cells would become
filled with 0's.

--
Regards,

RD

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

"Max" wrote in message
...
Try this to convert it all at one go to real numbers
Enter in any empty cell: 1, then copy that cell
Select the "second set of numbers", click Edit Paste special Multiply
OK
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,200 Files:354 Subscribers:53
xdemechanik
---
"Cathy" wrote:
I copied two sets of numbers based on two different queries from the same
source into a spreadhseet. The first set of numbers I copied into the
spreadsheet will sum up fine. The second set of numbers will not sum. I
tried reformatting the cells as number with 2 decimal places and then
when I
highlight the cells the bottom right tally shows a count not a sum as I'd
expect. There are no $ signs, no commas, etc. and I have already tried
reformatting the cells in various different number formats (number with 2
decimals, currency, etc.) and that doesn't help. If I manually type over
the
figure in the cell it will then be included in the sum total (without
changing any formatting). This is a very large spreadsheet and it is not
practical to retype everything. Is there a way to reformat the cells
which
I've not tried that might work? Any other suggestions to get them to
total
up?

Thanks in advance for any help you can render.

Can't sum a series of numbers
 

I tried both the paste special multiply and add and neither worked. I would
appreciate any other suggestions to get this to work.

"Max" wrote:

Try this to convert it all at one go to real numbers
Enter in any empty cell: 1, then copy that cell
Select the "second set of numbers", click Edit Paste special Multiply OK
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,200 Files:354 Subscribers:53
xdemechanik
---
"Cathy" wrote:
I copied two sets of numbers based on two different queries from the same
source into a spreadhseet. The first set of numbers I copied into the
spreadsheet will sum up fine. The second set of numbers will not sum. I
tried reformatting the cells as number with 2 decimal places and then when I
highlight the cells the bottom right tally shows a count not a sum as I'd
expect. There are no $ signs, no commas, etc. and I have already tried
reformatting the cells in various different number formats (number with 2
decimals, currency, etc.) and that doesn't help. If I manually type over the
figure in the cell it will then be included in the sum total (without
changing any formatting). This is a very large spreadsheet and it is not
practical to retype everything. Is there a way to reformat the cells which
I've not tried that might work? Any other suggestions to get them to total
up?

Thanks in advance for any help you can render.

Can't sum a series of numbers
 

If the multiply or add methods won't work make sure you first format the
cells as General, including the blank copied cell.


Gord Dibben MS Excel MVP

On Thu, 24 Jul 2008 17:01:00 -0700, Cathy
wrote:

Thanks for the repsonse. I tried both the multiply and the add special paste
and neither worked. I'm still manually keying data to get it to work but
would really appreciate anything else to try to get the sum to work.

"RagDyer" wrote:

Max, FWIW
One point about the "multiply" option as opposed to the "add" option.

If some of the cells are not contiguous, with the "add" option, you could
select an all encompassing block of cells, with an easy single selection,
that could go beyond the cells in question, and include empty and/or blank
surrounding cells.

If you tried this with the "multiply" option, those empty cells would become
filled with 0's.

--
Regards,

RD

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

"Max" wrote in message
...
Try this to convert it all at one go to real numbers
Enter in any empty cell: 1, then copy that cell
Select the "second set of numbers", click Edit Paste special Multiply
OK
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,200 Files:354 Subscribers:53
xdemechanik
---
"Cathy" wrote:
I copied two sets of numbers based on two different queries from the same
source into a spreadhseet. The first set of numbers I copied into the
spreadsheet will sum up fine. The second set of numbers will not sum. I
tried reformatting the cells as number with 2 decimal places and then
when I
highlight the cells the bottom right tally shows a count not a sum as I'd
expect. There are no $ signs, no commas, etc. and I have already tried
reformatting the cells in various different number formats (number with 2
decimals, currency, etc.) and that doesn't help. If I manually type over
the
figure in the cell it will then be included in the sum total (without
changing any formatting). This is a very large spreadsheet and it is not
practical to retype everything. Is there a way to reformat the cells
which
I've not tried that might work? Any other suggestions to get them to
total
up?

Thanks in advance for any help you can render.

Can't sum a series of numbers
 

It really wouldn't matter Gord, if that empty cell was a Text cell.

The "Paste Special", with either add or multiply would convert those "bad"
cells to calculable numbers.

--
Regards,

RD

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

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
If the multiply or add methods won't work make sure you first format the
cells as General, including the blank copied cell.


Gord Dibben MS Excel MVP

On Thu, 24 Jul 2008 17:01:00 -0700, Cathy

wrote:

Thanks for the repsonse. I tried both the multiply and the add special
paste
and neither worked. I'm still manually keying data to get it to work but
would really appreciate anything else to try to get the sum to work.

"RagDyer" wrote:

Max, FWIW
One point about the "multiply" option as opposed to the "add" option.

If some of the cells are not contiguous, with the "add" option, you
could
select an all encompassing block of cells, with an easy single
selection,
that could go beyond the cells in question, and include empty and/or
blank
surrounding cells.

If you tried this with the "multiply" option, those empty cells would
become
filled with 0's.

--
Regards,

RD

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

"Max" wrote in message
...
Try this to convert it all at one go to real numbers
Enter in any empty cell: 1, then copy that cell
Select the "second set of numbers", click Edit Paste special
Multiply
OK
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,200 Files:354 Subscribers:53
xdemechanik
---
"Cathy" wrote:
I copied two sets of numbers based on two different queries from the
same
source into a spreadhseet. The first set of numbers I copied into
the
spreadsheet will sum up fine. The second set of numbers will not
sum. I
tried reformatting the cells as number with 2 decimal places and then
when I
highlight the cells the bottom right tally shows a count not a sum as
I'd
expect. There are no $ signs, no commas, etc. and I have already
tried
reformatting the cells in various different number formats (number
with 2
decimals, currency, etc.) and that doesn't help. If I manually type
over
the
figure in the cell it will then be included in the sum total (without
changing any formatting). This is a very large spreadsheet and it is
not
practical to retype everything. Is there a way to reformat the cells
which
I've not tried that might work? Any other suggestions to get them to
total
up?

Thanks in advance for any help you can render.

Can't sum a series of numbers
 

Another guess would be that nemesis of imported data, the "non-breaking
space", char(160).

If your values have that character as a prefix or suffix, the data would
remain incalculable.

Try this:

Select the "bad" cells, then from the Menu Bar,
<Edit <Replace
In the "Find What" box, enter:
<Alt 0160
using the numbers from the Num keypad, *not* the numbers under the function
keys.

Leave the "Replace With" box empty,
And click <Replace All.

See if that works.

It may be that you perhaps need *both* procedures to convert your values.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Cathy" wrote in message
...
I tried both the paste special multiply and add and neither worked. I
would
appreciate any other suggestions to get this to work.

"Max" wrote:

Try this to convert it all at one go to real numbers
Enter in any empty cell: 1, then copy that cell
Select the "second set of numbers", click Edit Paste special Multiply
OK
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,200 Files:354 Subscribers:53
xdemechanik
---
"Cathy" wrote:
I copied two sets of numbers based on two different queries from the
same
source into a spreadhseet. The first set of numbers I copied into the
spreadsheet will sum up fine. The second set of numbers will not sum.
I
tried reformatting the cells as number with 2 decimal places and then
when I
highlight the cells the bottom right tally shows a count not a sum as
I'd
expect. There are no $ signs, no commas, etc. and I have already tried
reformatting the cells in various different number formats (number with
2
decimals, currency, etc.) and that doesn't help. If I manually type
over the
figure in the cell it will then be included in the sum total (without
changing any formatting). This is a very large spreadsheet and it is
not
practical to retype everything. Is there a way to reformat the cells
which
I've not tried that might work? Any other suggestions to get them to
total
up?

Thanks in advance for any help you can render.

Can't sum a series of numbers
 

You rae simply correct<g

Thanks RD


Gord

On Thu, 24 Jul 2008 17:19:47 -0700, "RagDyer" wrote:

It really wouldn't matter Gord, if that empty cell was a Text cell.

The "Paste Special", with either add or multiply would convert those "bad"
cells to calculable numbers.

Can't sum a series of numbers
 

Agreed, RD on the point of any empty cells within the range becoming zero,
albeit this won't affect the OP's intent to sum. The subtle point about the
multiply option is that by entering a "1" into an "empty" cell, that in
itself ensures that the "empty" cell will contain a: 1, and not an invisible
whitespace(s) <g. I use both methods.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads: 16,200, Files: 354, Subscribers: 53
xdemechanik
---
"RagDyer" wrote in message
...
Max, FWIW
One point about the "multiply" option as opposed to the "add" option.

If some of the cells are not contiguous, with the "add" option, you could
select an all encompassing block of cells, with an easy single selection,
that could go beyond the cells in question, and include empty and/or blank
surrounding cells.

If you tried this with the "multiply" option, those empty cells would
become filled with 0's.