countifs question
Hello all. I wonder if someone could provide some direction for the following:
I have created a simple stock management process for our IT department to control the ins/outs of "hotswap" laptop devices. We have created a column (Amber Level) which is an agreed level of stock for each department and device type. This is typically between 2 and 5 devices.
Once the Current stock drops near, on or below the amber level above, I need a formula to calculate a status in a separate field (hot swap status). I can then run conditional formatting to add Red Amber Green to each hot swap status, so the team know which device to prioritise building next.
So what I think I need is a countifs statement with an OR, but have no idea how to achieve it as all the examples I see are for 2 variables, whilst I need to accommodate a single countifs statement covering where
- Current Status is > amber level, then hotswap status is "No Action Required"
- Current Status is = amber level, then hotswap status is "Running Low"
- Current Status is < amber level, then hotswap status is "Build Now"
Thank you in advance for any help you might offer.
Answers
-
Hi @stuartr
I hope you're well and safe!
Try something like this.
= IF(OR([Current Status]@row = "", Amber@row = ""), "", IF([Current Status]@row > Amber@row, "No Action Required", IF([Current Status]@row = Amber@row, "Running Low", IF([Current Status]@row < Amber@row, "Build Now"))))
Did that work/help?
I hope that helps!
Be safe and have a fantastic week!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Wow @Andrée Starå - bullseye. It worked perfectly. Thanks so much for such a quick reply.
It seems so simple now, 3 separate if statements separated by a comma!
I hope you are also well
Kind regards
Stuart
-
Excellent!
You're more than welcome!
Sometimes, it's simpler than we think when we look at an issue. Been there, done that!
✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.4K Get Help
- 364 Global Discussions
- 200 Industry Talk
- 428 Announcements
- 4.4K Ideas & Feature Requests
- 137 Brandfolder
- 129 Just for fun
- 128 Community Job Board
- 446 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!