Thread: column won't sum

CK wrote...
>I was trying to figure out why my phone bill tripled this month so I copied
>and pasted the list of my alleged calls int an excel spreadsheet. when I
>tried to sum the column with the per call charges in it it is always zero.
>
>ie. in cell h235 I enter "=sum(h4:h234)" without quotes. The result is
>always zero. It doesn't matter whether I keep the source formatting or match
>destination formatting or change the formatting to number under "format
>cells." None of the paste special options are useful either. Also if I set
>the formula to say "=h34" it will correctly show the value from that cell.
>I'm at a loss.

Excel thinks your per call charges are all text. Try the following
formula.

=SUMPRODUCT(--SUBSTITUTE(H4:H234,CHAR(160),""))

It appears that your data is still text, even tho you've tired to re-format
it..........this is a difficulty much experienced with imported
data............even when you do =H34, you're probably still getting a TEXT
value.

Try using Data > TextToColumns > delimited > use NO delimiter (uncheck
everything) and choose {NONE} as the text qualifier..........hopefully this
will convert your TEXT numbers back to number-numbers.........

Vaya con Dios,
Chuck, CABGx3


"CK" <CK@discussions.microsoft.com> wrote in message
news:5B5C64B1-86D9-42EC-962E-F8B50C1BE701@microsoft.com...
> I was trying to figure out why my phone bill tripled this month so I
copied
> and pasted the list of my alleged calls int an excel spreadsheet. when I
> tried to sum the column with the per call charges in it it is always zero.
>
> ie. in cell h235 I enter "=sum(h4:h234)" without quotes. The result is
> always zero. It doesn't matter whether I keep the source formatting or
match
> destination formatting or change the formatting to number under "format
> cells." None of the paste special options are useful either. Also if I
set
> the formula to say "=h34" it will correctly show the value from that cell.
> I'm at a loss.

That worked, I'm not sure what it is but it worked
Thanks

"Harlan Grove" wrote:

> CK wrote...
> >I was trying to figure out why my phone bill tripled this month so I copied
> >and pasted the list of my alleged calls int an excel spreadsheet. when I
> >tried to sum the column with the per call charges in it it is always zero.
> >
> >ie. in cell h235 I enter "=sum(h4:h234)" without quotes. The result is
> >always zero. It doesn't matter whether I keep the source formatting or match
> >destination formatting or change the formatting to number under "format
> >cells." None of the paste special options are useful either. Also if I set
> >the formula to say "=h34" it will correctly show the value from that cell.
> >I'm at a loss.
>
> Excel thinks your per call charges are all text. Try the following
> formula.
>
> =SUMPRODUCT(--SUBSTITUTE(H4:H234,CHAR(160),""))
>
>

Your solution almost worked too but i needed to paste into a *.txt first and
import that and select space delimited. After that it worked like butter.

"CLR" wrote:

> It appears that your data is still text, even tho you've tired to re-format
> it..........this is a difficulty much experienced with imported
> data............even when you do =H34, you're probably still getting a TEXT
> value.
>
> Try using Data > TextToColumns > delimited > use NO delimiter (uncheck
> everything) and choose {NONE} as the text qualifier..........hopefully this
> will convert your TEXT numbers back to number-numbers.........
>
> Vaya con Dios,
> Chuck, CABGx3
>
>
> "CK" <CK@discussions.microsoft.com> wrote in message
> news:5B5C64B1-86D9-42EC-962E-F8B50C1BE701@microsoft.com...
> > I was trying to figure out why my phone bill tripled this month so I
> copied
> > and pasted the list of my alleged calls int an excel spreadsheet. when I
> > tried to sum the column with the per call charges in it it is always zero.
> >
> > ie. in cell h235 I enter "=sum(h4:h234)" without quotes. The result is
> > always zero. It doesn't matter whether I keep the source formatting or
> match
> > destination formatting or change the formatting to number under "format
> > cells." None of the paste special options are useful either. Also if I
> set
> > the formula to say "=h34" it will correctly show the value from that cell.
> > I'm at a loss.
>
>
>

CK wrote...
>I was trying to figure out why my phone bill tripled this month so I copied
>and pasted the list of my alleged calls int an excel spreadsheet. when I
>tried to sum the column with the per call charges in it it is always zero.
>
>ie. in cell h235 I enter "=sum(h4:h234)" without quotes. The result is
>always zero. It doesn't matter whether I keep the source formatting or match
>destination formatting or change the formatting to number under "format
>cells." None of the paste special options are useful either. Also if I set
>the formula to say "=h34" it will correctly show the value from that cell.
>I'm at a loss.

Excel thinks your per call charges are all text. Try the following
formula.

=SUMPRODUCT(--SUBSTITUTE(H4:H234,CHAR(160),""))

It appears that your data is still text, even tho you've tired to re-format
it..........this is a difficulty much experienced with imported
data............even when you do =H34, you're probably still getting a TEXT
value.

Try using Data > TextToColumns > delimited > use NO delimiter (uncheck
everything) and choose {NONE} as the text qualifier..........hopefully this
will convert your TEXT numbers back to number-numbers.........

Vaya con Dios,
Chuck, CABGx3


"CK" <CK@discussions.microsoft.com> wrote in message
news:5B5C64B1-86D9-42EC-962E-F8B50C1BE701@microsoft.com...
> I was trying to figure out why my phone bill tripled this month so I
copied
> and pasted the list of my alleged calls int an excel spreadsheet. when I
> tried to sum the column with the per call charges in it it is always zero.
>
> ie. in cell h235 I enter "=sum(h4:h234)" without quotes. The result is
> always zero. It doesn't matter whether I keep the source formatting or
match
> destination formatting or change the formatting to number under "format
> cells." None of the paste special options are useful either. Also if I
set
> the formula to say "=h34" it will correctly show the value from that cell.
> I'm at a loss.

That worked, I'm not sure what it is but it worked
Thanks

"Harlan Grove" wrote:

> CK wrote...
> >I was trying to figure out why my phone bill tripled this month so I copied
> >and pasted the list of my alleged calls int an excel spreadsheet. when I
> >tried to sum the column with the per call charges in it it is always zero.
> >
> >ie. in cell h235 I enter "=sum(h4:h234)" without quotes. The result is
> >always zero. It doesn't matter whether I keep the source formatting or match
> >destination formatting or change the formatting to number under "format
> >cells." None of the paste special options are useful either. Also if I set
> >the formula to say "=h34" it will correctly show the value from that cell.
> >I'm at a loss.
>
> Excel thinks your per call charges are all text. Try the following
> formula.
>
> =SUMPRODUCT(--SUBSTITUTE(H4:H234,CHAR(160),""))
>
>

Your solution almost worked too but i needed to paste into a *.txt first and
import that and select space delimited. After that it worked like butter.

"CLR" wrote:

> It appears that your data is still text, even tho you've tired to re-format
> it..........this is a difficulty much experienced with imported
> data............even when you do =H34, you're probably still getting a TEXT
> value.
>
> Try using Data > TextToColumns > delimited > use NO delimiter (uncheck
> everything) and choose {NONE} as the text qualifier..........hopefully this
> will convert your TEXT numbers back to number-numbers.........
>
> Vaya con Dios,
> Chuck, CABGx3
>
>
> "CK" <CK@discussions.microsoft.com> wrote in message
> news:5B5C64B1-86D9-42EC-962E-F8B50C1BE701@microsoft.com...
> > I was trying to figure out why my phone bill tripled this month so I
> copied
> > and pasted the list of my alleged calls int an excel spreadsheet. when I
> > tried to sum the column with the per call charges in it it is always zero.
> >
> > ie. in cell h235 I enter "=sum(h4:h234)" without quotes. The result is
> > always zero. It doesn't matter whether I keep the source formatting or
> match
> > destination formatting or change the formatting to number under "format
> > cells." None of the paste special options are useful either. Also if I
> set
> > the formula to say "=h34" it will correctly show the value from that cell.
> > I'm at a loss.
>
>
>

CK wrote...
>I was trying to figure out why my phone bill tripled this month so I copied
>and pasted the list of my alleged calls int an excel spreadsheet. when I
>tried to sum the column with the per call charges in it it is always zero.
>
>ie. in cell h235 I enter "=sum(h4:h234)" without quotes. The result is
>always zero. It doesn't matter whether I keep the source formatting or match
>destination formatting or change the formatting to number under "format
>cells." None of the paste special options are useful either. Also if I set
>the formula to say "=h34" it will correctly show the value from that cell.
>I'm at a loss.

Excel thinks your per call charges are all text. Try the following
formula.

=SUMPRODUCT(--SUBSTITUTE(H4:H234,CHAR(160),""))

It appears that your data is still text, even tho you've tired to re-format
it..........this is a difficulty much experienced with imported
data............even when you do =H34, you're probably still getting a TEXT
value.

Try using Data > TextToColumns > delimited > use NO delimiter (uncheck
everything) and choose {NONE} as the text qualifier..........hopefully this
will convert your TEXT numbers back to number-numbers.........

Vaya con Dios,
Chuck, CABGx3


"CK" <CK@discussions.microsoft.com> wrote in message
news:5B5C64B1-86D9-42EC-962E-F8B50C1BE701@microsoft.com...
> I was trying to figure out why my phone bill tripled this month so I
copied
> and pasted the list of my alleged calls int an excel spreadsheet. when I
> tried to sum the column with the per call charges in it it is always zero.
>
> ie. in cell h235 I enter "=sum(h4:h234)" without quotes. The result is
> always zero. It doesn't matter whether I keep the source formatting or
match
> destination formatting or change the formatting to number under "format
> cells." None of the paste special options are useful either. Also if I
set
> the formula to say "=h34" it will correctly show the value from that cell.
> I'm at a loss.

That worked, I'm not sure what it is but it worked
Thanks

"Harlan Grove" wrote:

> CK wrote...
> >I was trying to figure out why my phone bill tripled this month so I copied
> >and pasted the list of my alleged calls int an excel spreadsheet. when I
> >tried to sum the column with the per call charges in it it is always zero.
> >
> >ie. in cell h235 I enter "=sum(h4:h234)" without quotes. The result is
> >always zero. It doesn't matter whether I keep the source formatting or match
> >destination formatting or change the formatting to number under "format
> >cells." None of the paste special options are useful either. Also if I set
> >the formula to say "=h34" it will correctly show the value from that cell.
> >I'm at a loss.
>
> Excel thinks your per call charges are all text. Try the following
> formula.
>
> =SUMPRODUCT(--SUBSTITUTE(H4:H234,CHAR(160),""))
>
>

Your solution almost worked too but i needed to paste into a *.txt first and
import that and select space delimited. After that it worked like butter.

"CLR" wrote:

> It appears that your data is still text, even tho you've tired to re-format
> it..........this is a difficulty much experienced with imported
> data............even when you do =H34, you're probably still getting a TEXT
> value.
>
> Try using Data > TextToColumns > delimited > use NO delimiter (uncheck
> everything) and choose {NONE} as the text qualifier..........hopefully this
> will convert your TEXT numbers back to number-numbers.........
>
> Vaya con Dios,
> Chuck, CABGx3
>
>
> "CK" <CK@discussions.microsoft.com> wrote in message
> news:5B5C64B1-86D9-42EC-962E-F8B50C1BE701@microsoft.com...
> > I was trying to figure out why my phone bill tripled this month so I
> copied
> > and pasted the list of my alleged calls int an excel spreadsheet. when I
> > tried to sum the column with the per call charges in it it is always zero.
> >
> > ie. in cell h235 I enter "=sum(h4:h234)" without quotes. The result is
> > always zero. It doesn't matter whether I keep the source formatting or
> match
> > destination formatting or change the formatting to number under "format
> > cells." None of the paste special options are useful either. Also if I
> set
> > the formula to say "=h34" it will correctly show the value from that cell.
> > I'm at a loss.
>
>
>

CK wrote...
>I was trying to figure out why my phone bill tripled this month so I copied
>and pasted the list of my alleged calls int an excel spreadsheet. when I
>tried to sum the column with the per call charges in it it is always zero.
>
>ie. in cell h235 I enter "=sum(h4:h234)" without quotes. The result is
>always zero. It doesn't matter whether I keep the source formatting or match
>destination formatting or change the formatting to number under "format
>cells." None of the paste special options are useful either. Also if I set
>the formula to say "=h34" it will correctly show the value from that cell.
>I'm at a loss.

Excel thinks your per call charges are all text. Try the following
formula.

=SUMPRODUCT(--SUBSTITUTE(H4:H234,CHAR(160),""))

It appears that your data is still text, even tho you've tired to re-format
it..........this is a difficulty much experienced with imported
data............even when you do =H34, you're probably still getting a TEXT
value.

Try using Data > TextToColumns > delimited > use NO delimiter (uncheck
everything) and choose {NONE} as the text qualifier..........hopefully this
will convert your TEXT numbers back to number-numbers.........

Vaya con Dios,
Chuck, CABGx3


"CK" <CK@discussions.microsoft.com> wrote in message
news:5B5C64B1-86D9-42EC-962E-F8B50C1BE701@microsoft.com...
> I was trying to figure out why my phone bill tripled this month so I
copied
> and pasted the list of my alleged calls int an excel spreadsheet. when I
> tried to sum the column with the per call charges in it it is always zero.
>
> ie. in cell h235 I enter "=sum(h4:h234)" without quotes. The result is
> always zero. It doesn't matter whether I keep the source formatting or
match
> destination formatting or change the formatting to number under "format
> cells." None of the paste special options are useful either. Also if I
set
> the formula to say "=h34" it will correctly show the value from that cell.
> I'm at a loss.

That worked, I'm not sure what it is but it worked
Thanks

"Harlan Grove" wrote:

> CK wrote...
> >I was trying to figure out why my phone bill tripled this month so I copied
> >and pasted the list of my alleged calls int an excel spreadsheet. when I
> >tried to sum the column with the per call charges in it it is always zero.
> >
> >ie. in cell h235 I enter "=sum(h4:h234)" without quotes. The result is
> >always zero. It doesn't matter whether I keep the source formatting or match
> >destination formatting or change the formatting to number under "format
> >cells." None of the paste special options are useful either. Also if I set
> >the formula to say "=h34" it will correctly show the value from that cell.
> >I'm at a loss.
>
> Excel thinks your per call charges are all text. Try the following
> formula.
>
> =SUMPRODUCT(--SUBSTITUTE(H4:H234,CHAR(160),""))
>
>