1

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?

  • Hey widdy, if my answer was sufficient can you green tick it as an accepted answer? – Datanovice Sep 26 '18 at 6:49
0

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)
0

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:Two-Sheet Approach

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.