Formula: if checkbox is checked then add or subtract
Hi everyone, I would like to have a sheet that tells me how many goods we have left in our warehouse.
The idea is that employees report via a dashboard whether they are taking something out of stock or returning something.
If one checks the column "IN" or "OUT" they also have to add the amount. That amount should be subtracted from the total quantity when taking something out of stock (if the "OUT" column is checked). The amount that they bring back must be added (if column "IN" is checked).
The formula should be in column "Quantity left".
I know this is certainly possible in smartsheet, but I can't put together the right formula.
Can anyone give me a solution or point me in the right direction.
Thanks!
Best Answer
-
The way I would do this is set up two SUMIFS statements.
One SUMIFS would Sum together all the values in the Amount column IF the "Action" matches the action in this current row and IF the IN box is checked.
The second SUMIFS would do the same, but only if the OUT box is checked.
Then once you have these two total values, you can + and - them from the Total Quantity.
Try something like this:
=[Total Quantity]@row + SUMIFS(Amount:Amount, Action:Action, Action@row, In:In, 1) - SUMIFS(Amount:Amount, Action:Action, Action@row, Out:Out, 1)
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
Instead of the dashboard, I mean form**** .
-
The way I would do this is set up two SUMIFS statements.
One SUMIFS would Sum together all the values in the Amount column IF the "Action" matches the action in this current row and IF the IN box is checked.
The second SUMIFS would do the same, but only if the OUT box is checked.
Then once you have these two total values, you can + and - them from the Total Quantity.
Try something like this:
=[Total Quantity]@row + SUMIFS(Amount:Amount, Action:Action, Action@row, In:In, 1) - SUMIFS(Amount:Amount, Action:Action, Action@row, Out:Out, 1)
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Hey Genevieve P.
Thank you so much!!
Cheers,
Warehouse Tomorrowland
-
No problem! I'm glad I could help 🙂
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
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!