Is there such a formula as IF/AND/OR in Google Sheets?

Question

I'm working on this spreadsheet, field B10.

https://docs.google.com/spreadsheets/d/1wC-y0T9QeF6MZALavljzqVLJTfadSHmGt0yOFYgIMnE/edit?usp=sharing

I'd like to apply this formula, but I'm not sure how:

IF B10=TRUE AND B5=TRUE, THEN C10xG6
BUT 
IF B10=TRUE AND B5=FALSE, THEN C10*F6.
IF B10=FALSE, " ". 

Any help would be appreciated!

Answer 1

I believe this formula is more understandable and adherent to the request for a beginner:

=ifs(
and(B10=TRUE , B5=TRUE) , C10*G6,
and(B10=TRUE ,B5=FALSE) , C10*F6,
B10=FALSE, ""
)

Answer 2

Indeed, as already shown, two nested IF() functions solve your problem (See the Nested_Outcome formula in the Examples section)

I want to show another way of calculating for combined conditions, without the IF() function.

Let's present all combinations of your conditions in the form of a table:

(I wrote "Something else" to show that for the combination of B10=FALSE AND B5=TRUE some other option is possible, and not just a space, as in your case)

Considering that FALSE is equal to 0 and TRUE is equal to 1 (at least for Google Spreadsheets this is the case), let's rewrite this table in a general form:

You can get the option number from the B5 and B10 values with a very simple formula: =B10*2+B5+1

Now use the CHOOSE() function: specify this expression as the first parameter index, and then list all four possible options separated by commas -

=CHOOSE(B10*2+B5+1,"","",C10*F6,C10*G6)

Of course, for two conditions, the benefit from this approach is not very noticeable. But the same principle can be used for both three and four conditions - it is enough to multiply the value of each condition by the next power of 2:

=CHOOSE(4th_condition*8+3rd_condition*4+2nd_condition*2+1st_condition+1,...)

Yes, the formula will be quite long. But in any case, it will be shorter and clearer than eight nested IF() functions.

Answer 3

=IF(B10=TRUE,IF(B5=TRUE,C10*G6,C10*F6),"")

Hope it helps!

Answer 4

Your pseudo code example:

IF B10=TRUE AND B5=TRUE, THEN C10xG6 
BUT 
IF B10=TRUE AND B5=FALSE, THEN C10*F6.
IF B10=FALSE, " ". 

How to accomplish in google sheets:

=if(n(B10)=n(whatthefoxsay())," ",if(B5,multiply(C10,G6),multiply(C10,F6)))