Using multiple AND conditions in one IFS fromula

Question

I’m trying to make sheets calculate my delivery times based on which locations the items are shipped between.

Tried combining the Ifs and AND formulas, but I keep getting errors.

(Let’s say the delivery time between London and Barcelona is 5 days. Delivery time between Stockholm and Madrid is 6 days. A3=today’s date)

My formula looks like this right now:

=Ifs(And(A1=London,A2=Barcelona), A3+5, AND(A1=Stockholm,A2=Madrid), A3+6 

And so on. I have at least 11 different delivery times depending on destinations. How can I make this formula run?

Answer 1

I think you have a few problems,

firstly are you passing two conditions ? Must A1 be London and A2 be Barcelona for the formula to equate to true?

secondly, you need to wrap any strings in " " so Google sheets knows it's a string and not an integer.

This worked for me :

=Ifs(And(A2="London",A3="Barcelona"), A4+1)

https://docs.google.com/spreadsheets/d/1cwQv28S9T99JQsJbDX0JtQnoEYeAUivo6pyaHpGsgkw/edit?usp=sharing

updated formula to have 7 possibilities, basically you put your bracket in the incorrect place, read the docs next time, I know formulas can be scary at first but they follow a set logic =)

  =Ifs(And(A2="London",A3="Barcelona"), A4+1,
AND(A2="Stockholm", A3="Madrid"),A4+4, 
AND(A2="Germany", A3="Birmingham"),A4+15,
AND(A2="India", A3="China"),A4+5,
AND(A2="Glasgow", A3="Taiwan"),A4+75,
AND(A2="Munich", A3="Portugal"),A4+25,
AND(A2="North Korea", A3="South Korea"),A4+4)

Answer 2

You'd have more flexibility — and a much shorter formula — if you set up a separate sheet where you kept your destinations and hours, then had the first sheet reference that in a VLOOKUP, like this:

=A3 + VLOOKUP("*"&A1&"*"&A2&"*", 'Trip Times'!A:B, 2, FALSE)