Formulas for calculate number of days depending on list of value

Options

Hi there,

I need to know if it is feasible to obtain this formula :

In a sheet, i can choose several option from a list of value ( checkboxes).

If option A is checked, i add 1.5 days for ex. If i choose B, i add 1 day. If i choose A & B, i obtain 2.5. And so one.

How can i create that in one formula? Any idea?

Many thanks in advance !!

Best Answer

Answers

  • emmanuel edon
    emmanuel edon ✭✭✭✭
    Options

    Hi Genevieve, wonderful, i will test it asap, i think it looks like perfect, many thanks!!

  • Genevieve P.
    Options

    Great! Glad to hear it 😊

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • emmanuel edon
    emmanuel edon ✭✭✭✭
    Options

    Hi Genevieve, me again

    I just tried this formula :

    Nb jour is depending the Modules columns. Here, i need to identify the number of row because the list is inside the same field Modules and not on distinct colums, is it possible? Many thanks !

  • Genevieve P.
    Options

    Hi @emmanuel edon

    Sorry for the delay - Thanks for providing the screen capture, this helps a lot. There are two differences from what I thought your sheet was like. The first is that you're referencing a multi-select column (the column called "Modules"), and the second is that your values are not 1 (which indicates a checked checkbox) but are text values (PRH, CA, etc).

    For a multi-select column, you'll want to use the HAS function instead, like so:

    =IF(HAS(Modules@row; "PRH"); 2; 0)

    This looks to see if the cell in the Modules column of that row contains the selection "PRH" and if it does, returns 2. Otherwise, returns 0.

    You will need to adjust this for each selection to specify what you want the values to be. Example:

    =IF(HAS(Modules@row; "CA"); 6; 0)


    Now you can put them together in your SUM function:

    =SUM(IF(HAS(Modules@row; "PRH"); 2; 0); IF(HAS(Modules@row; "CA"); 6; 0))


    Let me know if this makes sense or if you have any other questions!

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • emmanuel edon
    emmanuel edon ✭✭✭✭
    Options

    Hi Genevieve, it works perfectly, that's awesome, many thanks again for your help !!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!