Formulas and Functions

Archives for November 2018

How to configure a project assignment and the assignment date and show the rest of the fields diseab
I have a problem. When Smartsheet sends the form on request to an area. I need to know if after receiving a project from a form, to be able to assign a resource and the date of assignment I can only do it from an update? or there are other ways of being able to assign people and date of assignment, automatically without…
JOIN only if cell is not blank
Hi there, Is there a way I can use the JOIN function to merge the contents of an entire row from another sheet, but exclude rows that are blank? The contents of the other sheet is generated from a form (and being constantly updated), so referencing the entire column is the only option unfortunately. It seems like it should…
Find and replace formulas?
Does anyone know of a way to find and replace formulas in my sheet? Or to find and replace a portion of a formula that exists throughout my sheet?
Nested IF/AND
When I check Declined Date, it is not changing to "Declined". Can someone help? =IF(AND(NOT(ISBLANK([Submit to Design Agency]8)), ISBLANK([Submit for Approval]8), ISBLANK([Approval Date]8), [Declined Date]8 = 0), "Submitted to Design Agency", IF(AND(NOT(ISBLANK([Submit to Design Agency]8)), NOT(ISBLANK([Submit for…
Linking Sheets
I am working on a master SmartSheet to roll up other Sheets into one. When changes are made in the linked SmartSheets, this is not reflected in the master. If I add additional rows, my master is not updated What I am missing?
Assigning a date to a value
I'm working on a gantt chart that is tracking teams progress on a project using agile development. Business Case / Background: When the teams provide updates to when a task is going to be completed, they reference the name of the sprint we're in. So for example if "Task ABC" will be completed in Sprint 1A that means its…
Alert Rules
Is it possible when more than one row having same value( duplicate row is submitted) an alert message goes to the user that row already exist something like this..?
Formula to count instances of a word
I have a sheet that is referencing another sheet to provide counts on instances of certain values in different columns. What I'm stuck on is counting how many times a single word is used if other text is in the same cell. For example if I have a column call Fruit and I want to know how many cells contain the word "apple"…
Formula Help
Hello, Looking for a little quick assistance. Not sure why I can figure this out, but I guess I'm just not as intuitive with the SmartSheet logic. I have a metrics sheet that I am using to provide data from other sheets to populate a dashboard. I currently have this formula and similar formulas that pull data within a…
Average of lease $ amounts in a certain region
I would like to see the average lease amount per region. I am cross sheet referencing. The formula for the average works. But when I add in the second part (countifs) to make sure a PE GL Checkbox has been checked and to specify a region (I do a cell reference) it doesn't seem to work. For instance, in 1 region I do not…

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