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
-
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!
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!