Jan 19 2022 12:12 PM
Fullname: Mei Cheung
Contact email: Email address removed
Contact numbers: 07872160641
Reference chat case: 1034231325
Issue:
I cannot use SUM() function properly. I have copy & pasted sums from my bank Statements and tried to sum out group of numbers from the affected excel file but the sum is does not calculate -remains (0) each time. I have also tried changing the format conditioning as numbers, text, and general but still the same issue. thks!
Jan 19 2022 03:43 PM - edited Jan 22 2022 11:40 PM
@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.
Jan 20 2022 11:29 PM
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
Jan 20 2022 11:42 PM
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.
Jan 21 2022 12:28 AM
You may try something like
=SUM( --A1:A10 )
That could work, depends on what is your actual data is.
Jan 21 2022 11:16 AM
@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.
Jan 21 2022 11:34 AM
FYI: ability to attach file is not for everyone, I have no idea what is the logic behind. Workaround the file could be attached to private message or share on OneDrive or like.
Jan 22 2022 10:18 PM
Jan 22 2022 10:29 PM
Jan 22 2022 10:43 PM
@meicheung Can't really tell why this problem occurred after upgrading to MS365. Had a brief look at the file and noted two things. The "numbers" in column H contain leading spaces and the negative numbers have a CR prefix. You need to get rid of these so that they become real numbers with which you can calculate. Have used Find&Replace (ctrl-H) to do just that.
The end result is in column K.