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.

    Forumla To Sum up Goods in and Out from a seperate Sheet

    ✭✭
    edited 02/17/25 in Formulas and Functions

    Morning

    Hoping this is a simple one but I have struggled to get it working :(

    This is my master sheet called Data Main and this is where we hold all the movements data whether a product has been goods in or goods out

    image.png


    What I need is another sheet which has a summary of the total qty goods in and out for each product, so the end result would look like this however I cannot work out what formula to add into the Goods in and out column. I should have renamed Device to Product sorry

    image.png
    · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Answers

    • ✭✭✭✭✭

      Hello @WWARRIOR2018

      Try this:
      Goods In Column:

      =SUMIFS({QTY},{Movement Type},"Goods In")

      Goods Out Column:
      =SUMIFS({QTY},{Movement Type},"Goods Out")

      Melissa Yamada
      melissa@insightfulsheets.com
      Data made simple, spreadsheets reimagined

      · Share on FacebookShare on Twitter
    • ✭✭

      Hi Melissa

      Thanks for the quick reply, sorry if I didnt explain it properly

      There is 2 sheets, one is called Main data where all the transactions are and the one we need to fix is called metrics

      Metrics sheet will have a list of the products on and next to it a column for goods in and goods out which will need populating.

      So the below is the Metrics sheet which I need the formula please, the forumla will be referencing the sheet Main Data to get the figures from

      image.png
      · Share on FacebookShare on Twitter
    • ✭✭
      edited 02/17/25

      I did try this which failed :(

      =SUMIFS({DATA MAIN Range 4}, {QTY}, "Goods In", {Movement Type}, Product@row)

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

      Hello @WWARRIOR2018

      Try this:
      Goods In Column:

      =SUMIFS({QTY},{Movement Type},"Goods In",{Product},Product@row)

      Goods Out Column:
      =SUMIFS({QTY},{Movement Type},"Goods Out",{Product},Product@row)

      Make sure to reference the correct column on your source sheet:
      Qty - column that you'll want to sum

      Movement Type - Goods In or Goods Out

      Product - column where product is

      Melissa Yamada
      melissa@insightfulsheets.com
      Data made simple, spreadsheets reimagined

      · Share on FacebookShare on Twitter
    • ✭✭

      Melissa

      Thank you so much for explaining this one, it worked perfectly once I sorted the correct references as you mentioned.

      · Share on FacebookShare on Twitter
    • ✭✭

      Hi @Melissa Yamada I was hoping you may be able to help with this one

      I am still working on this same smartsheet but want to add a stock valuation

      The below is the table where the data needs to go in stock valuation collum

      image.png

      What I need the formula to do is basically lookup the "PRODUCT in my lookup file below and get the cost price and then multiply that cost price by the current stock level.

      image.png

      I hope this makes sense and thank you again for all your amazing help

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

      Hello @WWARRIOR2018

      Try this:
      =INDEX(COLLECT({Cost},{Product},Product@row),1)*[Current Stock Level]@row

      Melissa Yamada
      melissa@insightfulsheets.com
      Data made simple, spreadsheets reimagined

      · Share on FacebookShare on Twitter
    • ✭✭

      Melissa thank you that worked perfect, I owe you a drink :) have a lovely weekend

      · 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