Google-sheets – Automatic value depending on drop down list

google sheets

I have a drop list in D24, you can choose between Junior, Mid and Senior.

I want E24 to have a different value depending on the choice.
Junior= 21.43€ , Mid= 28.57€ , Senior= 35,71€

enter image description here

Then, I want to calculate my total amount depending on the number of people, rate and day. (people x rate) x Days

How do I do?

Best Answer

You can try a formula with IF to display some results based on conditions. 

    =IF(D24="Junior",21.43,
      If(D24="Mid",28.57,
        IF(D24="Senior",35.71,"Wrong entry (D column)")
        )
       )

Then, I want to calculate my total amount depending on the number of people, rate and day. (people x rate) x Days

You can try to calculate by row, then sum the result.

First row =(C24*E24*F24)

Assuming there's 3 row : =ARRAYFORMULA(C24:C27*E24:E27*F24:F27)

Then sum everything : =SUM(ARRAYFORMULA(C24:C27*E24:E27*F24:F27))

Related Topic