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
https://docs.google.com/spreadsheets/d/1b0qkrOKrlWzEpyPkhBiDCy1ytcM2YIbM57O1HbOHqVM/edit#gid=0
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
Correct?
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
Okay
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
Oh
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
c5
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
Gotcha
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
https://docs.google.com/spreadsheets/d/1b0qkrOKrlWzEpyPkhBiDCy1ytcM2YIbM57O1HbOHqVM/edit?usp=sharing
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
Perfect
User
12/03/2018 - 02:26
good
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
https://www.techwalla.com/articles/how-do-i-use-a-letter-to-represent-a-value-in-excel
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
yes
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
okay
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
Right
Excelchat Expert
12/03/2018 - 02:54
That would work
