Skip to content
  • Try Smartsheet

    • Archived 2017 Posts

    Archived 2017 Posts

    Welcome to the Smartsheet Forum Archives


    The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

    Is there a way to use LOOKUP to get a total qty for the reference if it's been entred multiple times

    ✭✭
    edited 12/09/19 in Archived 2017 Posts

    Hi, 

    I'm really not sure if I'm going about this the right way, but I'm trying to create a 'Bill of Materials' within Smartsheet for a construction project. As per the two pics attached, I would like to get the value of a product that is mentioned multiple times in a particular column. 



    For example, 4 of PRODUCT 2 is going to be used for the construction of Drawing 1 (Labelled simply as '1'), and 1 of PRODUCT 2 used for the construction of Drawing 2 (Labelled simply as '2'). 

    To get the bill of Materials I would like to Smartsheet to look up all the instances of PRODUCT 2 being referenced within confined table, and then get a some of the Qty of PRODUCT 2?

    Any help would be greatly appreciated. 

    Capture.JPG

    Capture2.JPG

    Tags:
    · Share on FacebookShare on Twitter

    Comments

    • ✭✭✭✭

      Matt,

      Try this formula:

      =SUMIF([Part Number]:[Part Number], [Part Number]15, Qty:Qty)

      See the attached screenshot.  The formula is in the "Grand Total" column, rather than the "Qty" column, because it cannot reference itself. Remember that square brackets [ & ] need to be used with column names that are more than one word.

      Regards,

      Jacob

      Example.jpeg

      · Share on FacebookShare on Twitter
    • ✭✭

      Nailed it! Thanks Jacob. 

      · Share on FacebookShare on Twitter
    This discussion has been closed.

    Quick Links

    Categories

    Trending Posts