how to combine multi formula conditions

Waleed
Waleed ✭✭✭✭
edited 06/29/24 in Formulas and Functions

Hi team,

Trying to create a vehicle luggage capacity calculator for small and large cars, Please find below,


Please find the below Formula:
=IF(AND(Passengers@row <= 4, [25 Kg]@row <= 2, [15 Kg]@row <= 1, [7 Kg]@row <= 1), "Yes", "No")

The formula above indicates the col [Result] is "Yes" in the ase of follows:

The number of paxx col [Passengers] is below 4
and the number of luggage for col [25 kg] is below 2
and the number of luggage for col [15 kg] is below 1
and the number of luggage for col [7 kg] is below 1
Otherwise, the col [Result] is equal to "No"

I struggled with no success to add another additional conditioned formula to the above formula with numbers changes,

I want to have the logic to indicate col [Result] with "Yes" in case the luggage numbers are <2 for col (25 kg) and <1 for co [15 kg] and <1 for col [7 kg] aaaaaaaaand also if luggage number <1 for col (25 kg) and <2 for co [15 kg] and <1 for col [7 kg] .

=IF(AND(Passengers@row <= 4, [25 Kg]@row <= 2, [15 Kg]@row <= 1, [7 Kg]@row <= 1), "Yes", "No"),(IF(AND(Passengers@row <= 4, [25 Kg]@row <=1, [15 Kg]@row <= 2, [7 Kg]@row <= 2), "Yes", "No")

in short, my formula works fine for one logic while i want to add many other logics and to combine two, three and more conditioned formulas to end with different selected cols numbers to end with the same results "Yes" and if not equal to my conditions to end with "No"

Thank you, your assistance is much appreciated










Best Answer

Answers

  • Waleed
    Waleed ✭✭✭✭
    edited 06/29/24

    @Genevieve P. @Nick Korna @Andrée Starå

    Can resist asking for assistance from Smartsheet Masters ☺️

  • dojones
    dojones ✭✭✭✭✭

    I'm not following all of your logic, but I suggest creating multiple columns to evaluate different formula conditions.

    Your first formula is yielding correct answer'

    Create a second formula to evaluate the next 4 conditions

    Crate a third formula to evaluate the next 4 conditions.

    Final result can look if all 3 formula columns are Yes, then Yes other wise no.

    You could combine all 3 into a single formula, but it is easier to debug if done in parts.

  • Waleed
    Waleed ✭✭✭✭
    edited 06/30/24

    Hi @dojones

    Actually im into another related formula and still could not correct it as follow

    Formula using :
    =IF(AND(Paxx@row <= 4, [25 Kg]@row * 110, [15 Kg]@row * 80, [7 Kg]@row * 35 <= 400), "Yes", "No")

    Logic for Yes / No in this formula is

    if the number of passengers is <4
    and the sum of the number of col [25 kg] multiply 110
    Plus, Sum of numbthe er of col [15 kg] multiply 80
    Plus, Sum of the number of col [7 Kg ] multiply 35

    and If the sum of all the above is below 400 than the result is "Yes" Otherwise it is "No"

    in this example, the sum is the amount 345 (which is below 400) and accordingly it should be "Yes" , but its not working and ended with an error INVALID DATA TYPE



  • dojones
    dojones ✭✭✭✭✭
    Answer ✓

    Add Sum in the formula

    =IF(AND(PAXX@row <= 4, SUM([25 Kg]@row * 110, [15 Kg]@row * 80, [7 Kg]@row * 35) <= 400), "Yes", "No")

  • Waleed
    Waleed ✭✭✭✭

    Many Thanks @dojones
    It works well for us, much appreciated .. your assistance was much helpful

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!