Excel - IF Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

i am using sum to work out balances imported from a csv. If there is no number in the blank column the sum does not work but if i enter 0 and then delete it it works. What is the work around on that? Not had this before
Solved by F. D. in 35 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 04/09/2018 - 11:45
Hello, I understand that you need help with getting the sum of a column but it is not working correctly, right?
User 04/09/2018 - 11:46
Yes I am creating a bank staement in excel and want to use sum to calculate balance, but as I have imported them as csvs if there is no number in one of the columns it reads VALUE
Excelchat Expert 04/09/2018 - 11:46
I see. I can help you with this. Before we proceed, this is a reminder that our policy is 1 question per session so for this session we'll be resolving this particular question. We also currently do not support VBA/Macro solutions.
User 04/09/2018 - 11:47
Excelchat Expert 04/09/2018 - 11:47
There's a couple of reasons why this is happening and it would help if you could share your file. Even just the column that you are trying to sum, you can remove everything else.
User 04/09/2018 - 11:49
is this ok?
Excelchat Expert 04/09/2018 - 11:50
It would help if the actual file is uploaded since when you paste the values in the sheet to the right, it gets reformatted.
Excelchat Expert 04/09/2018 - 11:50
You can try summing in the sheet to the right and it may already work but that's because of the way Google sheet works.
Excelchat Expert 04/09/2018 - 11:54
Still with me?
User 04/09/2018 - 11:54
yes sorry, phone call... i am just editing file so I dont send you confidential info :-)
Excelchat Expert 04/09/2018 - 11:54
Excelchat Expert 04/09/2018 - 11:55
Just don't forget to extend the session when you are prompted if we aren't done on time.
User 04/09/2018 - 11:56
here you go
[Uploaded an Excel file]
Excelchat Expert 04/09/2018 - 11:57
Thank you. So you want to subtract the sum of Paid out and Paid in to the value on the previous line?
Excelchat Expert 04/09/2018 - 11:58
Is that what you are trying to do?
User 04/09/2018 - 11:58
yes so, balance on previous line, add paid in less paid out equals new balance
Excelchat Expert 04/09/2018 - 11:59
Okay, the reason for the error the user of + in the SUM()
Excelchat Expert 04/09/2018 - 11:59
If you use +, it will not have a built-in error check, but if you use SUM(value1,value2) then it will check if the values you are trying to sum are valid and will not sum invalid entries.
Excelchat Expert 04/09/2018 - 11:59
So try this:
Excelchat Expert 04/09/2018 - 11:59
Excelchat Expert 04/09/2018 - 12:00
That's for G11, then you can drag it down, let me know if that's what you need.
User 04/09/2018 - 12:00
Thats strange as I have used + before on other sheets many times.
Excelchat Expert 04/09/2018 - 12:01
Yes, it will work most of the time but in cases like this today, it won't.
User 04/09/2018 - 12:01
That also means I would have to amke all the paid outs as minus numbers?
Excelchat Expert 04/09/2018 - 12:01
I'm not sure what that means, I really just converted your formula to a valid one, I did not change the logic at all.
Excelchat Expert 04/09/2018 - 12:02
What exactly is the logic of the formula?
User 04/09/2018 - 12:02
Ok I will do a work around. Thanks for your help
Excelchat Expert 04/09/2018 - 12:02
Please extend the session.
Excelchat Expert 04/09/2018 - 12:02
And try this, Write a letter in A1, write a number in B1 then try this formula:
Excelchat Expert 04/09/2018 - 12:02
=sum(A1+b1) and also try this =SUM(A1,B1)
Excelchat Expert 04/09/2018 - 12:03
You will see the difference in using + or , in SUM
Excelchat Expert 04/09/2018 - 12:03
It seems that you already left, I've provided the solution to the question so I'm now marking this as Done. Thank you for contacting Got It. Please provide a fair rating if you come back and read this.

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
The Allstate Corporation
United Parcel Service
Dell Inc