IF, THEN for stock inventory

Deb Lee
Deb Lee ✭✭
edited 03/10/21 in Formulas and Functions

Hi, I'm really new to this but couldn't seem to see in the forums - I've set up a PPE stock request form for the teams to fill out - which autopopulates in the respective columns, including the multi-select PPE column.

What I'm trying to do is set up an IF something is requested, THEN the columns which has the current stock levels will remove 1. But I don't know how to write the formula. I have tried: =IF[PPE:PPE],1"HARD HAT", -1[Hard hat:Hard hat]

Or can anyone advise on a formula that accounts for multi-selects to be counted because if they select for example, a hard hat, XL vest and safety glasses, how should I write the formula please?


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You are going to need to manually enter your starting inventory somewhere. That could be in another cell on the sheet, a Sheet Summary Field, or manually entered directly into the formula.

    For this example, I will say you are starting with 25 Hard Hats and will have the starting inventory directly in the formula.

    =25 - COUNTIFS(PPE:PPE, CONTAINS("Hard Hat", @cell))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!