not sure what formula to use

New Contributor

I need some help working out a formula


I have a drop down box which has the options : Per person, total cost and no cost

There is then a box to enter cost

Up the top of the page are cells to enter number of children/adults


I then want the totals cell to calculate:

IF Per person is selected then it will show the cost x the total amount of people 


IF total cost is selected then times the cost cell by 1


IF no cost is selected then return the value 0

3 Replies
Assuming number of persons is in A1, Cost is in B1, and drop-down is in A2, the formula in B2 is:
=CHOOSE(MATCH(A2,{“Per Person”,”Total Cost”,”No Cost”},0),A1*B1,B1,0)

Thank you so much. this works perfectly. 

It’s my pleasure to help you!