Excel - IF Function Problem - Expert Solution

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

I have a document that I use for bills and when a bill is paid we put an x in the cell showing that it has been paid. I am looking for a way to have a separate place on the sheet to add up all the totals of each bill but when I put an x in the cell that has been paid I want it to remove it from the equation for example, say rent is $500(a1), power is $50(a2) and water is $50(a3) I would put in the cell =a1+a2+a3 and in that cell it would tell me $600 but where I'm stuck is if I put an (X) in b2 under power saying its paid I want the $50 to be removed from the final equation making it $550
Solved by T. Q. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 12/03/2018 - 01:56
Hello. Are you able to share this document me?
Excelchat Expert 12/03/2018 - 01:56
The one you reference in your question
User 12/03/2018 - 01:56
Excelchat Expert 12/03/2018 - 01:57
I am blocked from it. You need to change the share settings on it so that anyone with a link can view it before I can access it.
User 12/03/2018 - 01:57
i can share what sthe email
User 12/03/2018 - 01:58
ok open
Excelchat Expert 12/03/2018 - 01:58
Cool. I'm in
Excelchat Expert 12/03/2018 - 01:58
Let me read through your question again to make sure I understand
Excelchat Expert 12/03/2018 - 01:59
Okay, I'm going to look through your sheet to see what the solution might be
Excelchat Expert 12/03/2018 - 02:00
Do you want the total to be for every column, or will column C and E have different totals, for example?
User 12/03/2018 - 02:01
for every person
Excelchat Expert 12/03/2018 - 02:01
Just an FYI, I have View Only access, so I cannot type any formulas into the Google Sheet
Excelchat Expert 12/03/2018 - 02:02
Oh so you want the rows to sum, not the columns
Excelchat Expert 12/03/2018 - 02:02
User 12/03/2018 - 02:02
if you look on the right you see the blue box i want for me to put an x in the box and that cell affet il do one as an example
Excelchat Expert 12/03/2018 - 02:04
User 12/03/2018 - 02:04
when i put an x in d5 to take the 882 away
User 12/03/2018 - 02:05
like make the equasion subtract that total
User 12/03/2018 - 02:05
am i explaining right
Excelchat Expert 12/03/2018 - 02:06
I think we're on the right track. What is 883 being taken from? Which cell?
Excelchat Expert 12/03/2018 - 02:06
Excelchat Expert 12/03/2018 - 02:06
You did the example in this workspace
Excelchat Expert 12/03/2018 - 02:06
Sorry I was looking at the Google Sheet you sent me
User 12/03/2018 - 02:07
like make th x = to 0 so in the equashion it and blank = 1 ?
User 12/03/2018 - 02:07
Excelchat Expert 12/03/2018 - 02:08
Gotcha. Would it be okay if we did it in steps? For example, I make a sum equation in column Q and then we can sum column Q in the next step?
Excelchat Expert 12/03/2018 - 02:08
I'll do an example and you tell me
User 12/03/2018 - 02:09
ya fine juat need a box with final totals im going to hilight the sorse cells that will eventuly be blank and not to touch
Excelchat Expert 12/03/2018 - 02:10
Sounds good
User 12/03/2018 - 02:10
pink is what the sorse equashions are
Excelchat Expert 12/03/2018 - 02:10
User 12/03/2018 - 02:10
numbers sorry
Excelchat Expert 12/03/2018 - 02:10
No problem
Excelchat Expert 12/03/2018 - 02:12
What if instead of an X, you simply put the amount paid?
User 12/03/2018 - 02:13
im dealing with people who have no computer knowledge they just want a total its and i go in and add it up every month
Excelchat Expert 12/03/2018 - 02:13
Let me show you what I mean.
User 12/03/2018 - 02:14
i can do somthing instead of blank if eisier
User 12/03/2018 - 02:14
i had an idea but dont know how it will work though
Excelchat Expert 12/03/2018 - 02:15
So instead of X's, I put the amount that was paid. Now in column Q, you can see the balance remaining
User 12/03/2018 - 02:18
do you get what im saying
Excelchat Expert 12/03/2018 - 02:18
I believe so. What is your idea?
Excelchat Expert 12/03/2018 - 02:18
Oh I see.
User 12/03/2018 - 02:19
sorry its kind of hard to explain
Excelchat Expert 12/03/2018 - 02:19
I understand it, but we would need another column to implement that
User 12/03/2018 - 02:19
replace o with any other letter or symbol
Excelchat Expert 12/03/2018 - 02:20
Do you need to have symbols? or can you use what I did and put the amount that was paid?
User 12/03/2018 - 02:20
and ya we can add anythiing this is for me they only have accsess to edit the pink cells i do everything else
Excelchat Expert 12/03/2018 - 02:20
Oh okay
Excelchat Expert 12/03/2018 - 02:21
Can you give me full access to your Google Sheet? I only have View Only now
Excelchat Expert 12/03/2018 - 02:21
I will set up the formulas for you in there
User 12/03/2018 - 02:22
Excelchat Expert 12/03/2018 - 02:23
You have to change the share settings on this screen
[Uploaded an Excel file]
Excelchat Expert 12/03/2018 - 02:26
User 12/03/2018 - 02:26
User 12/03/2018 - 02:27
if you look at the blue box that is what they want to look at and know who to pay who
User 12/03/2018 - 02:29
for example u38 owes luke 120
User 12/03/2018 - 02:30
the left collum is whos is responsible and thr bills have letters in ( ) of who the money goes to
User 12/03/2018 - 02:31
and they just want to look at the blue box and know who to give the money to
Excelchat Expert 12/03/2018 - 02:31
Are you asking for a fomrula to go in there?
User 12/03/2018 - 02:32
so ahead and try it on ther bottem one and see if that works out i can edit it if it needs aome tweeking
Excelchat Expert 12/03/2018 - 02:33
Oh, each individual month is going to have a blue box?
User 12/03/2018 - 02:37
User 12/03/2018 - 02:37
i found this but i dont know how to make it on google sheets
User 12/03/2018 - 02:37
User 12/03/2018 - 02:38
yes each mont will have a blue box
Excelchat Expert 12/03/2018 - 02:38
You will have to set your sheet up differently in order for that to work correctly
User 12/03/2018 - 02:40
aah ok
Excelchat Expert 12/03/2018 - 02:46
How are you calculating who owes who what amount>
Excelchat Expert 12/03/2018 - 02:46
Excelchat Expert 12/03/2018 - 02:46
Like if someone is late in paying from a previous month, does that get factored into the current month?
Excelchat Expert 12/03/2018 - 02:46
This could end up being a very complex spreadsheet
User 12/03/2018 - 02:48
in the blue box when every thing is paid for that mont i want that cell to equil 0 or blank either one
Excelchat Expert 12/03/2018 - 02:48
User 12/03/2018 - 02:53
i can make it to what pink is editable and they do it this way
Excelchat Expert 12/03/2018 - 02:54
Excelchat Expert 12/03/2018 - 02:54
That would work
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