Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template to view 100+ formulas, including a glossary of every function and examples of commonly used and advanced formulas.

Formulas and Functions

Stumped by a formula? Ask for help or find examples to see how others use a formula to solve a similar problem.

Parent Children IF CONTAINS query
Hello! I've tried multiple versions of formulas that work for this at the parent level only, but I'm looking for a column formula that will not change the value of the children. I have a timesheet that has the period end date as the parent and the days within that period as the children. The last child includes a timecard…
IF and Statement for Color Statuses.
I'm trying to format rows to a yellow color when ‘Date Sent for Review’ and ‘Sent’ status are greater than 10 business days. How do I do this??
Math formulas across multiple cells
Hello all, Any help on the following issue would be very much appreciated. I have a scoring matrix built in to Smartsheet (See below). I would like to multiply the score of each cell for each criteria (Strategic Init Impact Score, Business Impact, etc.) by the corresponding weight (and divide each by 100) and then add…
Formula need to separate mixed text from a single column into separate columns
Using the LEFT or RIGHT formula currently as a means to separate the [Sales Rep Info] column. Problem is I can only figure out how to do it by number of characters. I need the formula to separate first and last name into a new column and a second formula to capture the information in the parenthesis. Same for the [Customer…
Finding Distinct Values in Two Columns
Hi, I was wondering if it was possible to find the number of distinct values in two columns. In this case I am looking to find if there is an overlap between start and completion dates for a given crew lead. Is there a reason that I am getting 1 as my output here? Is there any other way I can accomplish this?
Can this formula be fixed to get the correct time?
Hello, I am trying to come up with a formula to track time in Smartsheet. This is the formula I have: =IF([End Time]@row < [Start Time]@row, ((1200 + [End Time]@row) - [Start Time]@row) * 0.01, (([End Time]@row - [Start Time]@row) * 0.01)) After we hit a certain hour it tacks on 40 extra minutes. Is there a way to fix this…
Problem with % format in a cell
I have a cell in a summary sheet in which I am using a JOIN function simply to pull from another sheet. The cell in that sheet properly displays the data in a percent format (%) However the cell in my Summary sheet, displays the linked information as a decimal I have clicked the cell and toggled the format button off and…
Help using a multiple cell criteria from a source sheet to auto populate a formula sheet
I'm stuck. Really stuck. I've attempted more than 30 formulas. I hope someone can help me. I am using a master HR data file with 1500+ rows (reference/source sheet) to auto-populate a training enrollment roster (formula sheet), only I don't want every person listed on the source sheet added to my formula sheet. I need a…
Communication between 3 sheets
Hello and Help... Were are trying to do the following and I just can't wrap my head around it. There are 3 sheets that we'd like to communicate with one another. Truckload Inventory Log - A virtual parking lot of where loads are being held Receiving Log - A chronological log of loads received Shipping Log - A chronological…
#DIVIDE BY ZERO Error Resolution Attempt
Good afternoon! I am trying to replace the #DIVIDE BY ZERO to ready "Update the tasks first!" so I thought =IFERROR(AVG(CHILDREN()) <> 0, 100/ AVG(CHILDREN()), "") would do it. What is the syntax for this please? Thank you!

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Trending in Formulas and Functions

COUNTIF with AND
I've written the statement but am getting a #INCORRECT ARGUMENT SET error. I'm new to SS, any help is appreciated. =COUNTIF(AND([Opportunity Risk Path]:[Opportunity Risk Path], [Module Approval Status]:[Module Approval Status], ="Conventional", "Not Started"))
IF CONTAINS Formula to return different text values in second column
Hi! I am looking for some help in creating a formula where if a certain word is found in one column, another word is returned in a second column. For example if "Head", "Nasal", or "Spine" is contained in the Procedure column, "Neuro" is returned in the Clinical Specialty column. I've tried different variations of this and…
I need to result the column's name when using a JOIN(COLLECT) or INDEX(MATCH) formula
Previously, Row 1 of my smartsheet contained the column's name. This way, I could write a JOIN(COLLECT) formula to return the column's name if certain conditions were met throughout the sheet for that row. That worked until we applied column formulas to some of the rows. Now Row 1 can't be the column's name because of the…

Formulas and Functions

Help Article Resources

Categories

Popular Tags

Formulas and Functions

Discussion List

Help Article Resources

Categories

Popular Tags