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
ok
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
Sure.
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
=G10-SUM(D11,E11)
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.
