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

Matt Rentz
Matt Rentz ✭
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

  • Jacob H.
    Jacob H. ✭✭✭✭

    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
  • Matt Rentz
    Matt Rentz ✭

    Nailed it! Thanks Jacob. 

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

Categories