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.

Return the last value with a number
I have a sheet that I need to total at the top, but for a few columns, the "total" is not a sum or a max. It is a number that can go up or down each week and I just want the most current weeks data to show at the top. I have tried a few of the Smartsheet helps, but none of them are giving me exactly what I want. A…
How to Setup A Date Formula Based on a Conditional Statement
I have a date column for noting when someone's contracted employment will end. I'd like to create a formula to reference that date, subtract 180 days from it if their appointment term is "Limited" else the reminder date will be 90 days before the end date. I plan to use this reminder date for a workflow to alert someone.…
Date Visibility Logic Check
I have 3 dates (Calc, Planned and Actual). I want a logical function to check and pick the most accurate during the project lifecycle. It starts with a Calculated date and then Planned is updated as the proposals come in. As the vendor prepares to mobile we shift to an Actual Date. I can do 2 of the 3 but can't get that…
Weeknumber or other loop option
Hello Smartsheet community! I have a column where I insert a week number as a start week for a project and then I use it to map activities by week. Currently, I start the column with the first week of the project and then fill/drag it down to fill the column in order to feed into dashboards. My problem is that I have…
Formula for Lights
Hi all, I have a sheet that contains fields for "Preparer Due Date" and "Preparer Status" and am trying to add in the following logic to a formula: Blue = completed Green = task not started or in process that are due more than 3 days away Yellow = task not started or in process that are due within the next 3 days Red =…
Linking the same date from one cell to a second cell on same sheet
Hi Gang, I am having a hard time trying to use the date entered in one cell to another cell in the same row from the same sheet. The column I want as the target is RECEIPT DATE and the column for the duplicate date is START DATE. I have tried =[RECEIPT DATE]@Row but it didn't return anything at all. Thanks for the support,…
Is there a limit to adding VLOOKUPs?
I am working to sum multiple columns that match a specific criteria. I was able to accomplish this with the SUM formula and using VLOOKUP. It works when I add 2 VLOOKUP formulas together but if I add more, it says #NO MATCH. Is there a limit? Example: =SUM(VLOOKUP(Description@row, {Unit 3}, 2, false) +…
Hi all, I would like to set a formula to join data 1,2,3 but something is wrong as below picture.
Could you help me out pls? the main purpose is to join data from upper 1-3 without "/" Redundant in case there is no data at any cell among upper 1, 2, 3.
How to copy the cell values from diffrent workspace sheet into single sheet (Automate the process)
Hi Team, quick help needed. for example i have 10 project (each is a workspace), each project have 1 sheet with multiple columns and i need to fetch information of 4 columns from this sheet AND then put it on the separate sheet (can call it as master sheet) has information on project level i.e each row is a project in this…
IF statement using AND to generate colored symbol
Hello! Can somebody please help with the following formula? I am trying to generate the colored symbols (Green,Yellow,Red) based on the percentage of tasks complete along with the go live date. I am able to get a single If statement to generate the symbol but am failing at the AND one. Below is the formula that I'm using…

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Trending in Formulas and Functions

Formula Help: Do This Week, Do Next Week
Hi, gang! I need to create a formula that populates Do This Week, Do Next Week, Do Three Weeks for my boss. I have Start and Finish date columns as well as a dropdown Do column with those three as options. We're not looking for reports, but rather this summary view in Card View for him. Thank you for your help!
Giving an output based on text from multiple selection dropdown
Good Afternoon, I've been racking my brain trying to get this function to work. I have a column set up to allow multiple selections of specific text with the column name "Processes." I want the cell to find the specific text and give a number value. I'll then pull from a "Due Date" column giving the processes start date…
How to use IF and IFERROR?
I am writting the formula for counting the result of the work of my team. The result is counting from the complete date< expected date would be green and if it is greater, it would be red. However, in some cases, even i did not fill in the expected date and completed date, but the result is still showed as green. This…

Formulas and Functions

Help Article Resources

Categories

Popular Tags

Formulas and Functions

Discussion List

Help Article Resources

Categories

Popular Tags