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
edited 12/09/19 in Archived 2017 Posts


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. 





  • Jacob H.
    Jacob H. ✭✭✭✭


    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.




  • Nailed it! Thanks Jacob. 

This discussion has been closed.