Combined Use of IF, AND, OR Logical Functions in Google Doc Spreadsheet

33
2301
Combined Use of IF, AND, OR Logical Functions

Learn how to use important logical functions IF, AND, OR in Google Doc Spreadsheet. Logical functions are very easy to use if you get proper examples. You can find examples of logical functions online. But when you use logical functions in a combined form it expands the usefulness of some logical functions. It is rare to find simple examples of combined use of logical functions. Here I will explain you the combined use of IF, AND, OR logical functions in Google Sheets, the possible simplest way.

Some of you may familiar with Microsoft Excel Logical Functions. If you know if, and, or like logical functions in Excel you can similarly use it in Google Doc Spreadsheet also. There is no changes in the Syntax. It’s also relevant to combined use of IF, AND, OR logical functions. No changes in both spreadsheet applications.

It is easy to use logical functions individually. But logical functions are powerful when you use it in combined form. In this easy to follow Google Doc Spreadsheet tutorial we can learn how to use logical functions in combined form.

What are Spreadsheet Logical Functions?

I am not telling you the exact definition of logical functions. My intention is to give you a clear idea what logical functions are. With logical functions you can test the value of one cell and decide the result.

Logical Functions IF, AND, OR

In this example we are going to learn logical functions IF, AND, OR.

Use of Logical Function IF – How to

IF : This logical function is used to find whether an argument is true or false. If true this function brings one result and false another result.

Example : In Cell A1 put the value 50. Type any of the below formula in cell B1.

a) IF(A1>50,”Pass”, “Failed”)

or

b) IF(A1>50,”A1*20″, “A1*10”)

In the first logical test if the value in A1 is above 50, the Cell B1 shows “Pass”, else shows “Failed”

In the second logical test if the value in A1 is above 50, the Cell B1 get multiplies with 20, else get multiplies with 10.

Hope you understand this. Still doubt? Then switch to our dedicated IF function tutorial.

Use of Logical Function AND – How to

AND : This logical function is used to expand the use of IF function. So I will explain at later part of this tutorial.

=AND(TRUE, TRUE) All arguments are TRUE, result is TRUE

Use of Logical Function OR – How to

OR : Similarly above, this logical function is also used to expand the use of IF function.

=OR(TRUE,FALSE,TRUE) At least one argument is TRUE (TRUE)

I know you have doubt about AND, OR functions. You can learn it easily from the below example.

Learn IF, AND, OR logical functions easily from the below combined example.

How to use Logical Functions in Combined form in Excel Spreadsheet or Google Doc Spreadsheet?

You may have noticed that I’ve not detailed AND, OR logical functions above. It is because it is normally used to expand the usefulness of IF. So the below combined use of logical functions will clear your doubt about using these functions.

Similar to read! Simplified the Use of SUMIF function in Google Sheets

You may also interested in! How to use SUMIFS function in Google Sheets?

Download our eBook Now! [Google Sheets 20 proven tricks; that can boost your productivity – A must for all office goers!]

Example to Combined Use of IF, AND, OR Logical Functions

First type the contents as shown on the screenshot in a spread sheet. No matter whether you use Excel spreadsheet or Google Doc. You can omit Condition 1, Condition 2, Condition 3 because there we will apply logical functions later.

combined logical function example

Hope you have typed the above data.

Now the formula

=IF(OR(B3>49,C3>49,D3>49),”Passed”,”Failed”)

=IF(AND(B3>49,C3>49,D3>49),”Passed”,”Failed”)

=IF(OR(AND(B3>49,C3>49),AND(B3>49,D3>49),AND(C3>49,D3>49)),”Won”,”Failed”)

The first formula should be typed under condition 1, the second one under condition 2 and the final one under condition 3. If you copy and paste from the above, do rewrite the double quotes.

What these logical functions do?

1. Shows “Passed” if marks scored in any subject is >49

2. Shows “Passed” only if marks scored in all subjects are >49

3. Shows “Passed” if marks scored in any two subjects are >49

Note: Google Sheets SWITCH Function is more simpler than IF and IFS. Do you Know about it?

Conclusion

Go through the examples carefully several times. You should learn combined use of IF, AND, OR logical functions as it can greatly improve your spreadsheet skills. If you have any doubts drop it in comments below. Also you can get the similar results by using FILTER, AND, OR combination. Here instead of “Passed” or “Failed”, this combination filter the passed or failed students. Here is that New trendy tutorial.

Use of AND,OR with Google Sheets Filter Function [The above same sample data set used]

33 COMMENTS

  1. Helped me too. Thanks!
    In order to understand this, you first have to understand conditional checks and a bit of Boolean logic.

    I didn’t look too deeply, but do you have a primer on the available operators? I keep trying to do “!=” (not equal) in my IF conditions, and it..doesn’t work.
    e.g. =IF(B6!=0, A6/B6, “”)

    • “!” is only used if your referring to a cell that is on a different sheet. e.g. Sheet2!A1. Sheet2 = refers to the other sheet where the reference cell is locaed. A1 = is your reference cell. If your reference is on the same sheet, you should only apply the cell name itself.

      =IF(B6 = “0”, A6/B6, “”) — try this

    • It’s definitely a comma. Also one more thing. If anybody simply copy the function from my above post, it may not work. You may need to retype the double quotes symbol. Thanks.

  2. Hello,

    I need help with conditional formating of a google spreadsheet.
    I have in A1 List with “delivered” and “in work” and in A2 the delivery date.
    How can I adjust colors in that way to can have next:
    if delivery date >today – cell green
    if delivery date<today – cell red
    if delivery date=today – cell magenta
    This steps I allready resolved, but the problem is coming now:
    I want to make all row green even date is = than today if A1=”delivered”
    I tryed a lot f things, but always A2 keeps his color(green, red or magenta).

    Maby somebody can help me.

    Thank you!

    • Hi, It has nothing to do with conditional formatting.
      There is option in Conditional formatting
      Format>Conditional Formatting>Format cells if>date is after…
      Try it. Thanks.
      Sorry for the late reply to all. As I was busy with my job. Now on vacation.

  3. I am having a hard time with my conditional equation for google sheets. Here is my equation but it is not working. =IF(K:K=”NADINE”),AND IF(M:M =”X”),THAN (Q3+1).

    What I am trying to do is have google sheets check two columns for date and if they meet the criteria then calculate the number that matches the criteria. in another box. Can you help?

  4. Dear Prashant,
    Thank you for your help but there was a confusion when I tried to use the “if” as you explained ” IF(A1>50,”A1*20″, “A1*10”) : In the second logical test if the value in A1 is above 50, the Cell B1 get multiplies with 20, else get multiplies with 10.”
    There is no B cell in the example and need to use the formula without “” to mulitly else it will show up the text

    Here

  5. Hi, I am using a Google Sheets doc, where I need to make a spa menu formula. If D1 (the massage type) shows massage “a”, AND the hour cell D2 shows, 0.5 OR 1 OR 1.5 OR 2 (hours), then the price of the treatment will show in cell D3, in US dollars, as “$A”, “$B”, “$C”, or “$D” respectively. Is there a way to do this? It is worth noting I have about 30 treatments that need to be nested into 1 formula, in exactly this way…

    Thank you!

    • Welcome! You can do it simply by using the “IF” logical function. No need to go for the combined one. Just send me the sample database. I will try to solve it for you.

  6. Here is my issue — I thought this might solve it but I am not sure I follow
    I have multiple criteria. Here are the formulas
    =SUMIFS(F2:F, A2:A, “=January”, D2:D, “a la carte”)
    =SUMIFS(F2:F, A2:A, “=January”, D2:D, “=a la carte”)

    However – I now know that there is an additional option needed to land in the second cell “Broker”
    So the top would be in plain english “everything but a la carte OR broker”
    The second would be only those 2 [there are about 8 other options]

    Not sure how to tweak it — i’m a novice teaching this to myself

  7. Hi, Prashanth.
    I have a nested IF fomula where if a cell has specific values then the outcome is either 0 or 5 or 1 (see below). I have tried to use the IF(OR combination but in the examples given here, there are only two possible outcomes. I need three. How can this be achieved?
    =IF($A$8=0,0,
    IF($A$8=7,0,
    IF($A$8=10,0,
    IF($A$8=21,0,
    IF($A$8>10,5,1)))))

    • Hi,
      It’s not the correct way of using a nested IF. The above nested formula will return the following results;
      If Value in Cell A8=0,7,10 or 21, result will be 0
      If Value in Cell A8>10 and except 21, result will be 5
      If value in Cell A8<10 and not already specified above, the result will be 1
      Hope you understand what is the output of your formula.

  8. Hello,

    I have created the formula:
    =ARRAYFORMULA(IF(ROW(F:F)=1,”Profile”,if(B:B=””,””,if(AND(B2:B=”ST – Street”,C2:C=”No”,D2:D=”No”,E2:E=”Yes”),”ST ORDER ONLY INV”,”error”))))
    It is giving me the final “error” message I requested for everything, even though a few of my rows have the correct variables to result in the “st order only inv” message.
    If I point it to specific cell like so:
    =ARRAYFORMULA(IF(ROW(F:F)=1,”Profile”,if(B:B=””,””,if(AND(B2=”ST – Street”,C2=”No”,D2=”No”,E2=”Yes”),”ST ORDER ONLY INV”,”error”))))
    It gives me the correct “st order only inv” message.

    So to me that would show that it is currently looking at all my rows together as one big wad of information.
    So how would you tell it to look at each row by itself?

    Thanks for any help you could provide!

  9. Trying to enter a formula for an if/and function…Here is what I put: =if(and({Vernon!E33}>=10,{Vernon!E33:34}=19), “W”,”L”) where I have a google sheet that I am inputing information from other sheets within. It gives me the response L regardless of if E33>=10 or <10. Any suggestions?

    • Hi Daniel,

      See the example below

      Example 1
      Cell E33 Value = 11
      Cell E34 Value = 8
      Using the formula;
      =if(and(Vernon!E33>=10,sum(Vernon!E33:34)=19), "W","L")
      The result will be “W”
      In all other cases the result will be “L”

      The value in E33 can be from 11 to 19 and value in cell E34 can be from 0 to 8 depending the value in Cell E33. In any case the total should be 19. Other wise you will get the result “L”
      Thanks

  10. =IF(AND(J19=”Passed”,J28=”Passed”,J37=”Passed”),”Passed”,”Failed”)

    Why this doesn’t work? It gives me Error “Formula parse error.”

    • Hi Katrina,
      Welcome!
      The formula is perfect! But it doesn’t work!!
      The reason I stated on this site so many times.
      When you copy formula form this page or any other page on the web and then edit for your use, you should rewrite the double quotes.
      Thanks

  11. Hi

    Why is this giving me the wrong result when I have entered all the cells in the formula as 3?

    =IF(AND(J3>”2″,N3>”2″,R3>”2″,V3>”2″,Z3>”2″,AD3>”2″,AH3>”2″,AL3>”2″,AP3>”2″,AT3>”2″,AX3>”2″,BB3>”2″),”YES”,”NO”)

    I get NO

    ??

LEAVE A REPLY

Please enter your comment!
Please enter your name here