Help with a SUMIF formula 🙋♂️
I am having trouble with a SUMIF formula that is intended to calculate "returns" in the shipping/inventory database below:
My intention is that when a number is entered in the "Spare Qty" column underneath the "Returned" cell in the blue parent row for a specific SKU, that the "Returned" column in the far right automatically calculates and the "Remaining" column next to it updates as well.
The formula I have plugged in with no success is:
=(SUMIF(SKU:SKU, SKU@row, [Spare Qty]:[Spare Qty) - [Spare Qty]@row)
In this case, the 29 that is added for SKU 14314 underneath "Returned" in the blue parent row would ideally have populated above in the "Remaining" and in the "Returned" columns. I.e. For SKU 14314, it would be 29 in the returned column and 0 in the remaining column.
I also have other formula's at work in the spreadsheet that are more or less doing the same SUMIF equation but when I am trying to replicate it, it is unparseable. For example, when a number is added in the "In-Unit Count" column under the "Qty" cell in the blue parent row, the "Shipped" column and the "Remaining" column update correctly.
The formula at work in the "Shipped" column is:
=(SUMIF(SKU:SKU, SKU@row, [In-Unit Count]:[In-Unit Count]) - [In-Unit Count]@row)
The formula at work in the "Remaining" column is:
=([In-Unit Count]@row - (SUMIF(SKU:SKU, SKU@row, [In-Unit Count]:[In-Unit Count]) - [In-Unit Count]@row) + [Spare Qty]@row)
I might've been staring at this too long, but if anyone could offer some advice, it would be greatly appreciated!
Thanks,
Mark
Best Answer
-
It looks like you are just missing a square bracket after the second Spare Qty column reference...
=(SUMIF(SKU:SKU, SKU@row, [Spare Qty]:[Spare Qty]) - [Spare Qty]@row)
Answers
-
It looks like you are just missing a square bracket after the second Spare Qty column reference...
=(SUMIF(SKU:SKU, SKU@row, [Spare Qty]:[Spare Qty]) - [Spare Qty]@row)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!