IFS and AND functions possible in the same cell?

New Contributor



I was wondering if you are able to do an IFS and AND statement in the same cell. 


I was able to do an =IF(AND) statement, but would like to do multiple in the same cell and from what I understand it would need to be an =IFS instead of just =IF.  


For example, I am able to do the following:


However, I want to do multiple of these and change the "J5" to "J4" or "J3" etc. Is this possible or is there a better way to do this?


Any help is greatly appreciated!

-Hin Hin

2 Replies
best response confirmed by Hans Vogelaar (MVP)
short answer is yes it is possible. The only differences between IF() and IFS() is that IF() has only 1 conditional with 2 possible results/outputs. IFS() on the otherhand is a whole series of conditional - output pairs and it will 'go down the line' of conditionals until it finds one that is true and then return the result from that corresponding output. IFS() is the same as IF( condition1, out1, IF( condition2, out2, IF( condition3, out3, ... without all those nested IF() statements and the corresponding set of ))))))) at the end. There is 1 other difference in that ALL the entries in an IFS() statement are calculated (even for conditions that evaluate to be false) while non-array based IF() statements will only calculate either the true or false statements.
You can ALSO consider using SWITCH:
=SWITCH( IF(E4="No", D4, "xxx"), J5, K5, J4, K4, J3, K3, F4*L5)
Thank you for your response! The SWITCH function was actually incredibly helpful.