Using SUM and COLLECT To Get a Total
![cthrasher97141](https://us.v-cdn.net/6031209/uploads/drupal_profile/files/2019-12/8a/0e/n8a0e1d2c7c282c2f8e1ba6250c4a137b.jpg)
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
- Customer Resources
- 66.2K Get Help
- 431 Global Discussions
- 152 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 74 Community Job Board
- 501 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 306 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!