Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel returning "0" when summing
Hello!
I am having problems getting Excel to sum a column in a series of spreadsheets. The information comes from a SQL query and is imported into Excel. The problem is in column F, rows 2-141. No matter how I format the cells, I can't get a total. I have tried multiple suggestions found on the newsgroup (trim and clean macros, edit/replace with holding the alt key and typing 0160, etc.) and nothing works. The only way I can get it to total is copy/paste cells 2-141 into Word, then copy them out of Word and paste back over the same cells in Excel. It will calculate then, so something is being stripped out and allowing the calculation. If there were only a few spreadsheets to deal with, I would do the copy/paste in and out of Word each time. Unfortunately, there are 200 spreadsheets a week that are run and having this problem. I can't keep doing the copy/paste in and out of Word 200 times a week. If anyone has any suggestions, I would greatly appreciate them! Thank you in advance! Allison -- PhenyxFire Always five, acting as one. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel returning "0" when summing
On Thu, 28 Feb 2008 06:52:01 -0800, PhenyxFire
wrote: Hello! I am having problems getting Excel to sum a column in a series of spreadsheets. The information comes from a SQL query and is imported into Excel. The problem is in column F, rows 2-141. No matter how I format the cells, I can't get a total. I have tried multiple suggestions found on the newsgroup (trim and clean macros, edit/replace with holding the alt key and typing 0160, etc.) and nothing works. The only way I can get it to total is copy/paste cells 2-141 into Word, then copy them out of Word and paste back over the same cells in Excel. It will calculate then, so something is being stripped out and allowing the calculation. If there were only a few spreadsheets to deal with, I would do the copy/paste in and out of Word each time. Unfortunately, there are 200 spreadsheets a week that are run and having this problem. I can't keep doing the copy/paste in and out of Word 200 times a week. If anyone has any suggestions, I would greatly appreciate them! Thank you in advance! Allison Most likely, the cells are text. They may or may not have a nbsp (char(160)) in them. Changing the format after importing them will not make a difference. Several solutions: If all you need to do is SUM the column, you could try the simpler of one of these formulas that work, instead: =SUMPRODUCT(--F2:F141) =SUMPRODUCT(--(SUBSTITUTE(F2:F141,CHAR(160),""))) If you really need to convert them, after doing the find/replace <alt160 with nothing; enter a 1 in some cell; then EDIT/COPY; then select F2:F141; then EDIT/PASTE SPECIAL/Multiply --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hyperion Essbase Excel Add-in - returning text ("0") for null valu | Excel Discussion (Misc queries) | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
isna vlookup returning"0" instead of " " | Excel Worksheet Functions | |||
Returning a distance from a tabel, given "from" and "To"? | Excel Worksheet Functions | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel |