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?

Find more posts tagged with

Accepted answers

All comments

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?

lbradley

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å

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!

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)

Sign in to join the conversation!

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.