Help! Trying to figure out core problem in Smartsheet use case

sanjayb

We currently have a Smartsheet form that our rep uses when they are delivering products to outlets. The form has three fields -- one for the Outlet barcode, one for the Product barcode and one for the quantity delivered. If they are delivering Product A, B and C to Outlet 1, they must submit 3 forms -- one form per product. The form creates an activity log, from which data is pulled and feeds a master sheet for Outlets and a master sheet for Product inventory.

The rep would like to use only one form per Outlet visit in which they record all of the Products and Quantities delivered; thus the form would need to have multiple fields, i.e. Outlet, Product1, Quantity1, Product2, Quantity2, Product3, Quantity3, and so on. The difficulty is how we would pull the correct Quantity for the specified Product from this modified Smartsheet (screenshot below).

I have played with INDEX/MATCH formulas and can get the formula to pull the correct Quantity from this modified Smartsheet, but only if the range for one row is specified or if the Product appears only once in the range. MATCH does not work properly if the Product appears in multiple rows in the range.

Does anyone have any suggestions on how to resolve this?

Sanjay

Paul Newcome

Try adding a string of SUMIFS together.

=SUMIFS([prod1 qty]:[prod1 qty], [product1]:[product1], "Prod A") + SUMIFS([prod2 qty]:[prod2 qty], [product2]:[product2], "Prod A") + SUMIFS([prod3 qty]:[prod3 qty], [product3]:[product3], "Prod A")