# Microsoft Office Application Help - Excel Help forum > For Other Platforms(Mac, Google Docs, Mobile OS etc) >  > [SOLVED] Google Sheets: IF Formula on Checkbox

## zainmerchant

Hi

I have a Google Spreadsheet on which I want to use the IF formula on Checkboxes. What I'm trying to achieve is that if the box is checked (TRUE), the formula should give "Paid" and if it is unchecked (FALSE), the formula should give "Unpaid"

Since it's on Google Sheets I can't share the file itself but I have attached a screenshot. I used the formula =IF(E9=TRUE,"Paid","Unpaid") but this ends up giving an error each time.

----------


## vba_php

are those boxes actually PART of cells?  I wouldn't think so.  how did you add them?  I don't use goog shts.  according to both of these, it seems like the issue is that cell e9 is NOT a box, and there is no pointer associated, which makes perfect sense:

https://support.google.com/docs/answ...DDesktop&hl=en

https://support.google.com/a/users/answer/9308622?hl=en

looks like the code is right tho:

https://support.google.com/docs/answer/3093364?hl=en

----------


## davesexcel

One would think the checkbox would have to be linked to E9

----------


## zainmerchant

> are those boxes actually PART of cells?  I wouldn't think so.  how did you add them?  I don't use goog shts.  according to both of these, it seems like the issue is that cell e9 is NOT a box, and there is no pointer associated, which makes perfect sense:
> 
> https://support.google.com/docs/answ...DDesktop&hl=en
> 
> https://support.google.com/a/users/answer/9308622?hl=en
> 
> looks like the code is right tho:
> 
> https://support.google.com/docs/answer/3093364?hl=en




I've tried all these references but they don't work. And yes, these checkboxes are actually part of the cell. The checkbox was inserted using Data Validation - Checkbox (tickbox). I also have the option to insert value if TRUE/FALSE and the cell shows the Checkbox but you can see the value in the formula bar too. But when I use the IF function, I end up getting the same error.

----------


## zainmerchant

Any idea how it's done on Google Sheets?

----------


## vba_php

> One would think the checkbox would have to be linked to E9



thanks Dave.  I should have expected this from you, as per our altercations in the past.  My apologies for not giving all info the world has to offer as the answer.  I will go ahead and reply to the OP now if I can.

----------


## zainmerchant

Can you please explain what he said?

----------


## vba_php

are you talking to me, zain?  in excel, check boxes can be LINKED to cells, and thus, a reference, or sometimes called a ""pointer"" is created.  that allows code to be easily written to manipulate the boxes, regardless of what actions you want it connected to.

----------


## zainmerchant

> are you talking to me, zain?  in excel, check boxes can be LINKED to cells, and thus, a reference, or sometimes called a ""pointer"" is created.  that allows code to be easily written to manipulate the boxes, regardless of what actions you want it connected to.



Yes I was talking to you. Okay I get what you're saying. So I googled on how to link the checkbox to cells but I cant find anywhere how to do it on Google Sheets. Do you know to do it? If this works, then I guess the IF formula should work, hopefully.

----------


## vba_php

are you sure you looked it up?  this seems to explain it very well:

https://www.benlcollins.com/spreadsh...eets-checkbox/

does it not?

----------


## zainmerchant

> are you sure you looked it up?  this seems to explain it very well:
> 
> https://www.benlcollins.com/spreadsh...eets-checkbox/
> 
> does it not?



Yes it does explain it properly and as you can see in the screeshots attached I am doing exactly as it says but I still end up getting an ERROR for some reason which is what I need help with. I have attached a screenshot of the Formula used and the Data Validation applied on Cell E9 where the tickbox is

----------


## davesexcel

You may just need to reference E9 not use a formula.

2021-01-02_2-31-52.jpg

----------


## zainmerchant

I tried that way too but the second I use the IF formula, I get an error. Used cell reference to get "Paid" text in one cell. And then applied the IF formula on the cell with the text. Still getting the same error.

----------


## davesexcel

I didn't notice this earlier, the formula used in Post#11 is incorrect(extra quotation and bracket), it should be.


```
Please Login or Register  to view this content.
```

----------


## zainmerchant

Yeah I forgot to mention, I fixed that formula right after. Still getting the same error.

----------


## AliGW

*Administrative Note:*

Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below or the REPLY button instead of REPLY WITH QUOTE.

----------


## davesexcel

What is the formula you are using that is in error?

----------


## zainmerchant

The exact same formula that you posted above.

----------


## AliGW

Try this:

=if(F9=TRUE,"Paid","Unpaid")

If it's the same as Excel, then the tick box is just a TRUE/FALSE (1/0) switch, and the formatting you've set is not the underlying cell value.

----------


## davesexcel

I don't get an error with 
=if(F9="Paid","Paid","Unpaid")

Post #12 mentions just =E9 would be enough.

Also in Post #11 you are missing a quotation after "Paid

----------


## zainmerchant

Yeah but in some cases it might be Unpaid too so "=E9" wouldn't be enough. 

Basically whenever I use the IF function on Google Sheets, I get this error. I don't understand why. If I give you the link of the sheet, will you be able to see what the problem is exactly?

----------


## AliGW

Did you try my suggestion?

Yes, post a link.

----------


## zainmerchant

Yes I tried that too and a few other alternatives too but the second I put in the IF function, I get an error. I don't understand why. This exact same formula works on Excel 365 but not on Google Sheets.

Here's the link - https://docs.google.com/spreadsheets...t?usp=drivesdk

----------


## AliGW

If you hover over the formula bar, it tells you that the syntax needs semi-colons:

=IF(F9="Paid";"Paid";"Unpaid")

or:

=IF(B9="Paid";"Paid";"Unpaid")

From the tooltip:





> IF(logical_expression; value_if_true; value_if_false)
> 
> Example
> IF(A2 = "foo"; "A2 is foo"; "A2 is not foo")
> About
> Returns one value if a logical expression is 'TRUE' and another if it is 'FALSE'.
> logical_expression
> An expression or reference to a cell containing an expression that represents some logical value, i.e. 'TRUE' or 'FALSE'.
> value_if_true
> ...

----------


## zainmerchant

Ohhh thank you so much!

----------


## AliGW

If that takes care of your original question, please select _Thread Tools_ from the menu link above and mark this thread as SOLVED.  

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

----------

