Formulas for calculate number of days depending on list of value
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

Hi Emmanuel,
Yes, this is definitely possible! It would help to see how your sheet is set up, but essentially you could just add IF statements together for a total. You would need one IF statement per value, then add them together with + between.
Like so:
=IF([Column A]@row = 1, 1.5) + IF([Column B]@row = 1, 1)
The first 1 indicates if the box is checked. The second number indicates what the value is (1.5, 2, etc). You can add in a value if the box is not checked, but I have left this in order to indicate that it should be read as blank, in that case.
Should you need some help with this, please provide a screen capture of your sheet in grid view (removing sensitive data), explaining what each of the values are for your A, B, C columns.
Cheers!
Genevieve
Answers

Hi Emmanuel,
Yes, this is definitely possible! It would help to see how your sheet is set up, but essentially you could just add IF statements together for a total. You would need one IF statement per value, then add them together with + between.
Like so:
=IF([Column A]@row = 1, 1.5) + IF([Column B]@row = 1, 1)
The first 1 indicates if the box is checked. The second number indicates what the value is (1.5, 2, etc). You can add in a value if the box is not checked, but I have left this in order to indicate that it should be read as blank, in that case.
Should you need some help with this, please provide a screen capture of your sheet in grid view (removing sensitive data), explaining what each of the values are for your A, B, C columns.
Cheers!
Genevieve

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

Great! Glad to hear it 😊

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 !

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 multiselect 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 multiselect column, you'll want to use the HAS function instead, like so:
=IF(HAS([email protected]; "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([email protected]; "CA"); 6; 0)
Now you can put them together in your SUM function:
=SUM(IF(HAS([email protected]; "PRH"); 2; 0); IF(HAS([email protected]; "CA"); 6; 0))
Let me know if this makes sense or if you have any other questions!
Cheers,
Genevieve

Hi Genevieve, it works perfectly, that's awesome, many thanks again for your help !!
Help Article Resources
Categories
Check out the Formula Handbook template!