1

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?

0

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

  • I copy pasted the formula for the drop list and I get an error – SunSun Aug 5 at 9:58
  • Can you share what is the error ? What's the language of your sheet (formula will use ";" instead of "," based of country location) ? do you have any case sensitive issue ? (i.e. "Mid" =/= "mid") You can share a sample too, i'll be easier to fix issues. – pjmg Aug 5 at 10:02
  • I'm in France. Here is the sample: Sample – SunSun Aug 5 at 10:19
  • I'm french too. There's two solutions : 1/ Set your spreadsheet in english : Fichier > Paramètres de la feuille Otherwise you can try this formula. 2/ =SI(D24="Junior";21,43; SI(D24="Mid";28,57; SI(D24="Senior";35,71;"Wrong entry (D column)") ) ) I've replaced the commas (",") with semicolons (";") in the formulas and for the values (like 35,71) replaced the (".") with (","). For reference with the same formula, French version will be like =somme(12,3;14,5) English version will be like =sum(12.3,14.5) – pjmg Aug 5 at 10:31
  • My spreadsheet is set in english and it still doesn't work. Here is the sample : (drive.google.com/file/d/1jpO-ENN4v3PbmKqdV1bbQ5p42w4COLI1/…) I still get a parse error – SunSun Aug 5 at 10:39

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.