**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