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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!