Conditional quantities for pricing sheet?

BrianT ✭✭✭✭✭✭
edited 07/22/20 in Formulas and Functions


I am creating a pricing form and am stuck on a formula. In the example, the first 4 widgets cost $10. Each additional widget is after 4 widgets is $3. In the preceding cell [1-4 WIDGETS], I have a single-select dropdown of 1, 2, 3, 4, 5+ (as I need these numbers to count the number of widgets sold for another cell/formula). What I'm looking to do:

  1. If the number selected is 1, 2, 3, or 4, cost = $10 and widget quantity is counted per the customer's selection.
  2. If the number selected is 5+, cost = $10, count [1-4 WIDGETS] quantity as '4' AND execute logic that displays the next column of '[ADDITIONAL WIDGETS]' with 5, 6, 7, 8, etc..

I was going to use IFBLANK because I do not want to have a dollar value in cells that have not yet been selected (as this will mess up my totals in the summary reports).

I have a feeling that I am over-thinking this. Any thoughts?

Best Answer


  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Could you share some screenshots? Its hard to picture what you're talking about. Are you looking for a formula to calculate the cost based on 1-4 = 10 each and then $3 for every additional? Or are you trying to make a form display content correctly? If you could send us screenshots of the form, the output from the form when users select more than 4 widgets I am sure we could help come up with a viable solution.

  • BrianT
    BrianT ✭✭✭✭✭✭

    In [Widgets 1-4] the user will select the number of widgets. The first 4 widgets are $10, regardless of how many are selected. So, the price should calculate as $10 no matter what they choose. That said, I DO want to calculate the number of widgets sold, so '4' and '5+' should both calculate as '4'.

    EDIT: I changed the Help Text in the form to help me out here. I have abandoned the conditional logic and will probably use counts of the first two columns to determine the total number sold.

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    Answer ✓

    What about doing a nifty calculation and letting the user select specifically the number of units they want to buy? Then you could just have one column called Widgets.

    =IF([email protected] <= 4, [email protected] * 10, 4 * 10 + ([email protected] - 4) * 3)

    That would calculate your total widget price in one cell with one formula. Would that work?

  • BrianT
    BrianT ✭✭✭✭✭✭


    I just realized that I did not reply. Your solution worked! Thank you very much and apologies that it took a year for me to confirm your solution.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!