Forum Discussion
Excel Sum function
meicheung .... For quicker dispositive explanations, it is best if you attach an Excel file that demonstrates the problem.
I can understand your reluctance, because the data is from your bank statement. But I suspect that you pare down the problem to just 2 or 3 arbitrary numbers and a SUM function. That should not be too revealing.
From your description, I suspect that Excel is indeed is interpreting the data as text. Note that the SUM ignores text in ranges.
The format of the cell does not matter (for this problem; of course it might matter once the problem is solved). And appearances can be deceiving. Use formulas of the form =ISTEXT(A1) to confirm that A1 is indeed text.
There can be many reasons why Excel treats the data as text. Typical reasons....
1. The currency values use a currency symbol that is different from your system confirguation. For example, dollar sign ("$") instead of the symbol for yuans.
2. The currency values use the thousands separator and decimal point differently from your system confirguration.
3. The data contains non-breaking spaces (ASCII 160) instead of regular spaces (ASCII 32).
#3 is the easiest work-around to explain. In a parallel column (or row), enter formulas of the following form:
=--CLEAN(SUBSTITUE(SUBSTITUTE(A1, CHAR(160), ""), CHAR(32), ""))
ERRATA: The double-negate ("--") is necessary to convert the text to a numeric result. If it returns a #VALUE error, that means there is additional text that needs to be removed.
Select the original range (e.g. A1:A100), right-click and select the format that you want. Then copy the range in the parallel column, select the original range, right-click and select paste-special-value. If that remedies the problem with SUM, you can delete the parallel column.
If that does not work, you might need to work around #1 and #2. That is difficult to explain, because it depends on your version of Windows and Excel. After you provide that information in a response here, someone who is familiar with both versions should be able to direct you.
Hi Joe,
Thanks for yr detailed explanation but so sorry I don't understand what you are saying- I am complete non-tech dumb dumb person! I want to attach the excel file to you but sorry I am not familiar with hotmail's layout ( I hardly use it!) I can't see button wheree to attach it to you? I thoght that Ruth who guided me to you had done it already?!
kind thnsk!
Mei
meicheung wrote: ``I don't understand what you are saying``
Sorry about that. If Sergei's workaround does not work for you, try to follow the directions below to attach an Excel file to a response here.
Do not paste an image of the data. That is completely useless when we suspect that the type and content of the data (text, perhaps containing "invisible" characters) is the problem.
Be sure the Excel file duplicates the problem (SUM returns zero). Be sure to omit any personal identity data (account numbers, full names, etc).
If you do want to publish real balance and transaction numbers, reduce the data to 2 or 3 entries that cause SUM to return zero.
The click "browse" near the bottom of the reply pane, to wit:
When you click "browse", it opens a dialog box that allows you locate and select the file to be attached.
I hope that is clear. If not, I hope that Ruth can help you, whoever she is.
If the data you get could be pasted into excel and to not seems to be too compliacted.
You could use value to transfer them into number from text first in the next column. Then sum.
