SUMIF formula confusion // help?
I am reaching out as I am getting a little confused with a SUMIF problem. I've included a screenshot below to help illustrate.
So, the blue parent rows represent delivery phases and the device list at the top is the total contracted device count we get from a client.
The goal is that when a person enters a shipping quantity in the delivery phases below, the top total count adjusts accordingly and is represented in the "Remaining Quantity" column.
Using "Device SKU" as the unique identifier and subtracting from the "Quantity" column, I know this is possible... but just can't seem to make it work.
If anyone has any advice it would be greatly appreciated!
Answers
-
Hi Mark,
Since your chart is in the same sheet, the SUMIF formula will SUM your total quantities at the top. To work around this, you will need to use the SUMIF function, then minus that quantity @row, and finally minus that whole calculation from the quantity @ row.
Try this:
=Quantity@row - (SUMIF([Device SKU]:[Device SKU], [Device SKU]@row, Quantity:Quantity) - Quantity@row)
To break it down, here's just the SUMIF:
SUMIF([Device SKU]:[Device SKU], [Device SKU]@row, Quantity:Quantity)
This looks for the SKU number in that row, then SUMS all the values in the Quantity column, if that Device number matches (including the total number).
Then we minus the total number: - Quantity@row
And finally we take that entire calculation, and minus that number from the Quantity @row:
Quantity@row - (SUMIF(etc...
Let me know if this works for you!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Success!
Thank you for helping me wrap my head around this one Genevieve. It is very much appreciated.
Cheers,
Mark
-
Haha awesome! Happy to help!
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Is there a way to do the same referencing another sheet? Lets just say for example I have an "Inventory Sheet", and I want to subtract the Quantity from a "Inventory Pull Sheet" by Matching the skus?
This is the Inventory Sheet where I would like the subtraction to take place.
And this is the sheet I would like to reference the "quantity" to be subtracted by its respected sku.
-
Hi @Jimmy
It looks like Paul has provided you a solution on this other post, here: https://community.smartsheet.com/discussion/82033/inventory-automation-how-to-subtract-cell-data-from-one-sheet-to-another-by-matching-sku#latest
Feel free to tag me on that other post if you still have questions!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!