Sum not working

  • Thread starter Thread starter dawn
  • Start date Start date
D

dawn

I have done this in many other worksheets and it works
fine. I first import the data in as General format.
Then I use the IF function to find a quantity for each po
number and put on a printout. Normally then I just use
the Sum function to give me a total of quantities for
each po number. There is nothing no different about
these formulas but the sum is giving me a zero. If i go
in the quantity field and retype the number in (which I
don't want to do, this is why this is self-calculating)
then it gives the correct sum. I checked under error-
checking to see if the number is a text and it is not but
if I do an IStext it says true. If i convert to number
even though the error checker says it is not it still
gives me a zero!!!!! Everything is the same and i have
done this many times!!! Why is this not working!!! I
need this to total itself or there is no use for Excel.
This is pissing me off cause I need this now not
tomorrow!!
 
Hopefully 5 mins isn't too long to wait :-)

Copy an empty cell (Not one formatted as text though), select all your data and
do Edit / Paste Special / Add.

Changing the format of a cell alone will not make the data numeric if it has
already been set to text. You also need to coerce the data back to numeric by
means such as the above.
 
When you imported the values in they were imported as Text.

Copy an empty cell, select your "numbers" and choose Edit/Paste Special,
selecting the Values and Add radio buttons. Click OK.
 
guess what - i already did this numbers of times even
trying a multiply way before I sent this message and it
still doesn't work.
 
another thing about doing the paste special - when u
paste with all or value checked plus add or multiply
nothing will paste - it will be left blank but if u paste
special without the add or multiply but none than it
pastes.
 
thanks for all your help but lets think about this. If
my data fields were in fact text than the IF function
will not work and it does!! Funny isn't it, I have been
thru this before and know for a fact that that command
will most definitely not work with a text format. So if
this is working and I tried summing any data (from the
data source sheet and then also from the sheet with the
IF functions because it then already converted it) and
neither sheet works. Very strange indeed. Please Help
me...
 
Happy for you to send a sheet down to me so I can take a look. Need to take the
NOSPAM bit out of my email address though.

Only other thought that springs to mind at the moment, is that you might want to
run Dave McRitchie's Trimall macro over the data and then try again with the
paste-special, especially if the data came from the web at all.

http://www.mvps.org/dmcritchie/excel/join.htm#trimall
 
i keep forgetting something, another thing if it was text
than i would have a green triangle and an error would
appear asking me if i would like to convert the text to a
number. I also tried this =SUM(D29/1) - i tried dividing
it and then it gives me the VALUE so I evaluate the
formula and it gives me SUM("75"/1) first than I click
evaluate and then it gives me SUM(#VALUE!) which of
course doesn't help me.
thanks again for being so speedy
 
Would you care to translate this so it is understandable?


: another thing about doing the paste special - when u
: paste with all or value checked plus add or multiply
: nothing will paste - it will be left blank but if u paste
: special without the add or multiply but none than it
: pastes.
:
:
: >-----Original Message-----
: >Hopefully 5 mins isn't too long to wait :-)
: >
: >Copy an empty cell (Not one formatted as text though),
: select all your data and
: >do Edit / Paste Special / Add.
: >
: >Changing the format of a cell alone will not make the
: data numeric if it has
: >already been set to text. You also need to coerce the
: data back to numeric by
: >means such as the above.
: >
: >--
: >Regards
: > Ken....................... Microsoft MVP -
: Excel
: > Sys Spec - Win XP Pro / XL 00/02/03
: >
: >---------------------------------------------------------
: -------------------
: >It's easier to beg forgiveness than ask permission :-)
: >---------------------------------------------------------
: -------------------
: >
: >
: >
: message
: >: >> I have done this in many other worksheets and it works
: >> fine. I first import the data in as General format.
: >> Then I use the IF function to find a quantity for each
: po
: >> number and put on a printout. Normally then I just use
: >> the Sum function to give me a total of quantities for
: >> each po number. There is nothing no different about
: >> these formulas but the sum is giving me a zero. If i
: go
: >> in the quantity field and retype the number in (which I
: >> don't want to do, this is why this is self-calculating)
: >> then it gives the correct sum. I checked under error-
: >> checking to see if the number is a text and it is not
: but
: >> if I do an IStext it says true. If i convert to number
: >> even though the error checker says it is not it still
: >> gives me a zero!!!!! Everything is the same and i have
: >> done this many times!!! Why is this not working!!! I
: >> need this to total itself or there is no use for Excel.
: >> This is pissing me off cause I need this now not
: >> tomorrow!!
: >
: >
: >---
: >Outgoing mail is certified Virus Free.
: >Checked by AVG anti-virus system
: (http://www.grisoft.com).
: >Version: 6.0.605 / Virus Database: 385 - Release Date:
: 01/03/2004
: >
: >
: >.
: >
 
that is exactly what i did was copy and paste from a
website. I even tried resaving as external data and
reimporting in. The website u gave me is exactly what I
need but can I just copy and paste the code he wrote in
visual editor which i did but i tried to run macro which
is now there but nothing seems to happen, do I have to
edit this file somewhere?

thanks for all your help
 
Have you tried reimporting the data in? Could just be a bad import. (Also
perhaps import as number rather than general)

: thanks for all your help but lets think about this. If
: my data fields were in fact text than the IF function
: will not work and it does!! Funny isn't it, I have been
: thru this before and know for a fact that that command
: will most definitely not work with a text format. So if
: this is working and I tried summing any data (from the
: data source sheet and then also from the sheet with the
: IF functions because it then already converted it) and
: neither sheet works. Very strange indeed. Please Help
: me...
:
: >-----Original Message-----
: >Hopefully 5 mins isn't too long to wait :-)
: >
: >Copy an empty cell (Not one formatted as text though),
: select all your data and
: >do Edit / Paste Special / Add.
: >
: >Changing the format of a cell alone will not make the
: data numeric if it has
: >already been set to text. You also need to coerce the
: data back to numeric by
: >means such as the above.
: >
: >--
: >Regards
: > Ken....................... Microsoft MVP -
: Excel
: > Sys Spec - Win XP Pro / XL 00/02/03
: >
: >---------------------------------------------------------
: -------------------
: >It's easier to beg forgiveness than ask permission :-)
: >---------------------------------------------------------
: -------------------
: >
: >
: >
: message
: >: >> I have done this in many other worksheets and it works
: >> fine. I first import the data in as General format.
: >> Then I use the IF function to find a quantity for each
: po
: >> number and put on a printout. Normally then I just use
: >> the Sum function to give me a total of quantities for
: >> each po number. There is nothing no different about
: >> these formulas but the sum is giving me a zero. If i
: go
: >> in the quantity field and retype the number in (which I
: >> don't want to do, this is why this is self-calculating)
: >> then it gives the correct sum. I checked under error-
: >> checking to see if the number is a text and it is not
: but
: >> if I do an IStext it says true. If i convert to number
: >> even though the error checker says it is not it still
: >> gives me a zero!!!!! Everything is the same and i have
: >> done this many times!!! Why is this not working!!! I
: >> need this to total itself or there is no use for Excel.
: >> This is pissing me off cause I need this now not
: >> tomorrow!!
: >
: >
: >---
: >Outgoing mail is certified Virus Free.
: >Checked by AVG anti-virus system
: (http://www.grisoft.com).
: >Version: 6.0.605 / Virus Database: 385 - Release Date:
: 01/03/2004
: >
: >
: >.
: >
 
I love that bit of code of Dave's, and use it repeatedly at work and home.
Stick it in your personal.xls and it will always be available to you, whatever
document you are working on.
 
Back
Top