Skip to content
  • Try Smartsheet

    • Formulas and Functions

    Formulas and Functions

    Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

    Formula to Count Qty from an Order Sheet

    ✭✭✭✭✭✭
    in Formulas and Functions

    Hi

    I have an order sheet that holds the quantity of an item being ordered. The sheet also shows whether there is enough of the item in stock or not.

    image.png


    If the item is in stock I need to minus the qty from the corresponding item on the stock sheet. If the Item is not in stock, once the job is completed, it needs to add the qty made to the relevant item on the stock sheet.

    I think I probably need a few helper columns and some COLLECT formulas somewhere but, I'm happy to share the sheets with anyone that could help.

    TIA

    Cheryl

    Tags:
    · Share on FacebookShare on Twitter

    Best Answer

    • Community Champion
      Answer ✓

      Hi @Cheryl Collins

      Yes you would need some helper columns to calculate the Stocks.

      You can add new columns in your sheet with the help of INDEX/MATCH formula get the In Stock value in the same sheet now, add one more column called “Available Quantity” and do the calculations in these column.

      Let me know if you need more details on how this will work.

      Best Regards

      Amit Wadhwani, Smartsheet CoE, Ignatiuz Software, Exton, PA

      https://www.linkedin.com/in/amitinddr/

      Best Regards
      Amit Wadhwani      , Smartsheet Community Champion
      Smartsheet CoE, Ignatiuz, Inc., Exton, PA

      Did this answer help you? Show some love by marking this answer as "Insightful       💡" or "Awesome ❤️" and "Vote Up ⬆️"https://www.linkedin.com/in/amitinddr/

      · Share on FacebookShare on Twitter

    Answers

    • Community Champion
      Answer ✓

      Hi @Cheryl Collins

      Yes you would need some helper columns to calculate the Stocks.

      You can add new columns in your sheet with the help of INDEX/MATCH formula get the In Stock value in the same sheet now, add one more column called “Available Quantity” and do the calculations in these column.

      Let me know if you need more details on how this will work.

      Best Regards

      Amit Wadhwani, Smartsheet CoE, Ignatiuz Software, Exton, PA

      https://www.linkedin.com/in/amitinddr/

      Best Regards
      Amit Wadhwani      , Smartsheet Community Champion
      Smartsheet CoE, Ignatiuz, Inc., Exton, PA

      Did this answer help you? Show some love by marking this answer as "Insightful       💡" or "Awesome ❤️" and "Vote Up ⬆️"https://www.linkedin.com/in/amitinddr/

      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭✭

      Hi @Amit Wadhwani

      Thanks, let me have a go and I will come back to you if I get stuck! :-)

      Cheryl

      · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Help Article Resources

    Create and edit formulas in Smartsheet

    Formula combinations for cross sheet references

    Smartsheet functions list

    Quick Links

    Categories

    Want to practice working with formulas directly in Smartsheet?

    Check out the Formula Handbook template!

    Trending in Formulas and Functions