Excel Sum function

[meicheung]

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!

[#2]

@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.

[#3]

@Joe User 

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

[#4]

@meicheung 

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.

[#5]

@meicheung 

You may try something like

=SUM( --A1:A10 )

That could work, depends on what is your actual data is.

[#6]

@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.

[#7]

@Joe User 

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.

[#8]

@Joe User 

[#9]

Thank you for all you gentlemen's replies! Sorry, I find it hard to follow instructions to try them out! I hve just managed to find BROWSE and attach this file! I appreciate all yr help!
I hope u can open this 97-2003 Exel sheet. I had been using the version for last 10 years nicely until a friend subscribed me onto Office 365 when I starting getting troubles! Now I can't open older versions of 97-2003, someone taught me I had go thru 7 steps in order to open it up in 365!:downcast_face_with_sweat:

Ruth Reah is the Customer Services Assistant who tried to help & guided me to post my Question on Forum. I have copied our chat below, she says perhaps you guys can "Roll out some excel code to fix the formatting issue?" This would be great if you can do this! A simple method which I don't need to figure things out myself!!:folded_hands::face_with_tears_of_joy: thanks!!
------------------------------------------------------
January 19, 2022, 7:41 pm
mei
Hello Mei! Based on the investigation we just did on the excel file that you have right now. It appears that the problem is the specific file that you are working right now. I am afraid that there's need for you to manually recreate this file especially the numbers because when I tried to manually change it auto sum worked though.
-------------------------------------------
January 19, 2022, 7:52 pm
Ruth Reah
What I can advise mei is that, you can post this concern of yours to the excel mvp and editor's page. You can request for assistance in recreating this file or they can probably roll out some excel code to fix the formatting issue. In this way you don’t have to effortly recreate the file what you just need to do is wait until an editor is ready to provide resolution to it. What do you think about this Mei?

[#10]

@meicheung 

This is the dialogue box that comes up when I try to 2x click on SUMS box

[#11]

@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.