Using SUM and COLLECT To Get a Total

cthrasher97141

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!

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

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"))