If/Then statement with math equation

I am wanted to create a formula in a Smartsheet that references 1 cell with a "True" response to then subtract 1 from a different cell. For Example:

"if[Type of card}=Mobil, then - 1 from [#of cards left Row below]

I know this isn't correct verbiage. The data is coming off a form. There are 2 types of cards that can be entered. If it is one type, then the # of cards left would be reduced by one.

Is this possible? If so, how?

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Are you able to provide a screenshot for reference? Do you have an auto-number column or a Created (date) system generated column?

  • Here are the cells I'm referencing.

    If the card type is Kwik Trip, I would like to reduce the number of Kwik Trip cards on hand by the number of cards issued.

    There are two types of cards so I would be repeating it for the other type.

    Hope that makes sense!

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi @lbradley

    I hope you're well and safe!

    Try something like this.

    =100 - SUMIFS([How many cards?]:[How many cards?], [What type of card was given?]:[What type of card was given?], "Kwik Trip")

    Add the formula to the cell with 100 in your screenshot, and also update the starting amount (100) to what you have.

    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, Awesome, 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.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    I would suggest putting the 100 in a Sheet Summary field so that you can apply the below as a column formula:

    =[Starting Kwik Trip]# - SUMIFS([How many cards?]:[How many cards?], [What type of card was given?]:[What type of card was given?], "Kwik Trip")


    Since you have two different types, you can put the other starting number in another Sheet Summary Field and do something like this to have both counts in the same column but varying based on what is in that particular row:

    =IF([What type of card was given?]@row = "Kwik Trip", [Starting Kwik Trip]#, [Other Starting Number]#) - SUMIFS([How many cards?]:[How many cards?], [What type of card was given?]:[What type of card was given?], [What type of card was given?]@row)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!