countifs question

stuartr

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.

Andrée Starå

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!

stuartr

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

Andrée Starå

@stuartr

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!