Formulas and Functions

Archives for October 2020

Nesting a formula in an 'if' statement - conditional values
I have two reimbursement schemes for staff vs. managers for an expense, and am using a 'checkbox' to identify managers when submitting a claim. Managers will have a 'checked' checkbox in that column. I'm trying to use in a IF statement to behave in the following manner and can't get the syntax right. I'm striving for, in…
If text formula - if one cell = a name then another cell = an email address. See example below
I want to create a formula that looks at the Designer cells text and if that cell equals a certain designers name the Designer Email cell equals their email address I am a novice, as you can tell, and would appreciate help :) Thank you
Multiple IF/AND/OR/ Functions
Hi! I thought I had figured this out... but midway through making changes, realized that it's still not working quite as effectively has I thought. I'm currently using the below formula to change the Offer Phase parent row from "In Progress" to "Complete" if the following conditions are met: If the Offer phase in row 770…
Match any part of multi select to another multi select on another sheet
Hello, I have two separate sheets that each have multi select columns and I want to join a particular column if it has ANY mutual matches between the two. For example, on sheet 1, A B C are selected in the multi select and I want to find any matches on sheet 2 which contain anything selected from sheet 1. The column on…
Multiple IF/And statements in one column
I am using the function: =IF(AND(Impact@row = "Low", [Angela Approval]@row = "Approved", [Billye Approval]@row = "Approved", [Jason Approval]@row = "Approved", [Michael Approval]@row = "Approved", [CAB Approval]@row = "Approved"), "Approved"), IF(AND(Impact@row="Regular",[CAB Approval]@row = "Approved"),"Approved") When I…
how do I display the highest condition from the child cells in the parent cell?
for parent I the cell (1, 3) must show "Urgent) and for Parent 2 the cell (1,9) must show "Action"
I need to calculate the networkdays between 2 fields including when the end date is blank.
Column 1 titled "todays date" - this is the date the item is submitted Column 2 titled "manager response date"- this is the date in which a response was provided Column 2 may be blank. - Therefore in this case I need a formula that will show me the networkdays from the "todays date" column and consider Column 2 as a 0…
=NETDAYS FORMULA
Hi, I am trying to calculate how many days it has been since a unit was paid for however I am getting the #INVALID DATA TYPE error. I am assuming the issue is that one of the dates I am referencing is pulling the date from a different sheet using a VLOOKUP formula. Is that the issue and is there a work around for it? Any…
Ignoring Duplicates
Hi I added this data mesh to add inspection number based on date of inspection. Now we are getting two inspection for same building(in two different months) , but formula still using the initial month to formulate the Inspection number. is there any other way to ignore that, or am I doing anything wrong?
Can you use CountIF if there is more than one term in a cell?
I have a sheet that is updated from an export from another system. One of the fields in the other system is for category and can have more than one category selected. So when the data exports it appears in Smartsheet as "Category 1, Category 2" - separated by a comma. I want to count all the rows with Category 1. Is there…

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions

Can I copy a row to another sheet more than once?
Is it possible to copy a row to another sheet and have it copy multiple times on sheet #2? For example on sheet #1 I have a project with multiple dates for different deliverables within the overall project. I need these projects to copy over to a 2nd sheet where we get a separate line for each date. Right now we are…
Trying to determine if all children have N/A Status
I'm working on a formula for summary rows to look at the % Complete of the row as well as the statuses of the child rows. I have the % Complete portion handled: =IF([% Complete]@row = 1, "Complete", IF(AND([% Complete]@row > 0, [% Complete]@row < 1), "In Progress", IF([% Complete]@row = 0, "Not Started"))) I want to add in…
I am trying to manipulate my SUMIFs Formula
When Using SUMIFS(Range, Criterion Range 1,Criterion...) I have multiple columns for example Estimate Spend, 25% spend, 50% spend, Rather than having multiple %'s of spend I just want to be able to use 1 reference and multiply it by 0.25 or 0.5 in the first "Range" category so =SUMIFS({Spend} * 0.25, {Phase}, "phase 1",…

Formulas and Functions

Discussion List

Help Article Resources