Using SUM and COLLECT To Get a Total

I have 3 sheets total with the same columns; Identifier, Quantity, Status.

Sheet 1 has a form that collects supply request from a business unit; a user selects the supply needed as well as the quantity of the needed. A field named Status is used to determine if the supply has left inventory (SHIPPED).

Sheet 2 has a form to input supplies received into the wharehouse and placed in inventory; a user selects the supply received and the quantity received into inventory. A field named Status is used to determine if the supply has been received in inventory (RECEIVED).

Sheet 3 is a list of the supplies in inventory by a unique identifier and the quantity in inventory; this is used in a dynamic view.

I need to calculate in Sheet 3 the available quantity in inventory based on the items SHIPPED in Sheet 1 and the item RECEIVED in Sheet 2. I believe I would add up all of the items RECEIVED - the items departing SHIPPED for each item available in inventory by unique identifier. This quantity would be displayed in Sheet 3 for each unique item. so...

=SUM(COLLECT(Sheet 2)) - SUM(COLLECT(Sheet 1))

I cannot figure out how to write the formula..... Please Help, thank you in advance!

Answers

  • David Joyeuse
    David Joyeuse ✭✭✭✭✭

    Hi @cthrasher97141

    I would rather go with SUMIFS here instead of COLLECT.

    =SUMIFS({Sheet 2 - Quantity}, {Sheet 2 - Identifier}, Identifier@row, {Sheet 2 - Status}, "RECEIVED") - SUMIFS({Sheet 1 - Quantity}, {Sheet 1 - Identifier}, Identifier@row, {Sheet 1 - Status}, "SHIPPED")

    Hope it helped!

  • cthrasher97141
    cthrasher97141 ✭✭✭✭

    Thank you so much Davis!

    I finally came up with this monster


    =[Beginning Inventory Quantity]@row + SUM(COLLECT({Inventory Receivable Project Sheet - Qty Received}, {Inventory Receivable Project Sheet - INV Code}, "=[Item Number]@row", {Inventory Receivable Project Sheet - Status}, "=Received")) - SUM(COLLECT({Supply Orders Project Sheet - QTY Requested}, {Supply Orders Project Sheet - INV Code}, "=[Item Number]@row", {Supply Orders Project Sheet - Internal Status}, "=Shipped", {Supply Orders Project Sheet - Internal Status}, "=Received"))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!