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.

Product questions?
Ask it here! The community's got your back.

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

How can I find a position of a character in a string?
Hi all - I'm trying to find a position of a character within a string, which in theory seems simple - just use the FIND() function. What I'm struggling with is when there are multiple instances of the value, and I want to start at the 4th instance (for an example) of it. Here's an example of the String I'm trying to parse:…
Nested IF statement
Receiving an error with the formula and couldn't find anything similar within the existing posts. I have 3 columns (Testers) and each tester will have the option to choose if something passed, failed or is ready to retest. I'm trying to have the formula ready If Column A reads Passed, then read column B and if that read…
How to avoid counting a drop down option?
I have one drop down option which is used on 3 cells, 2 cells have "EVT" and "OK2Fab" and 1 cell "EVT" "OK2Fab" "OK2Tool", when I use: COUNTIF({WHS Hot Issue Tracker_NO Range 3}, AND(HAS(@cell, "OK2Fab"), HAS(@cell, "EVT"))) It counts 3 and when I use COUNTIF({WHS Hot Issue Tracker_NO Range 3}, AND(HAS(@cell, "EVT"),…
Formulas for year to date, quarterly, and monthly
Hi, Is there a way to do formulas for year to date, quarterly and monthly? And if so, how can I do that formula?
Conditional logic to look up cell entry
Hello, In my task tracking sheet, I have a status column (dropdown with one of the values being completed) and Completion Date column that I need help with. I want to be able to conditionally allow Status field to set to complete only if there is an entry in the completion date in the Completion date column. If the date is…
Countif two fields from two different sheets match
Hi All, I am working on a data summary table and want to try and make my life easier and not have to edit each cells formula. I would like to take two matching drop downs from two different sheets and if they match, count the rows the fields match, based also on some secondary requirements. My current calculation is…
Auto populate a date field when selecting a weekday
When I select Monday from a droplist column named WEEKDAY, I want next Mondays date to auto populate in date column named Date. Same for each day of the week. Is this possible?
Use MATCH to find the last position of a matching value.
In the period column I have a MATCH formula to get the position of the matching value in Current Month. =IFERROR(MATCH([Current Month]@row, June@row:May@row, 0), "") In this case the value returned is 5. How would I get the position of the last matching value, which would return 8? Thank you!
Nested IF with calculations - Unparseable Error
Hi! I am trying to clean up a leave tracking Smartsheet and am just having the hardest time getting my nested IF formula to work. I have been through countless articles in Community and still no luck. The goal is the add the correct amount of days, depending on the type of leave, to then return the date at which that type…
Conditional IF INDEX MATCH formula
I am trying to make a conditional index match statement where if "Topic A" is selected from a dropdown column called "Tags", the "Technical Lead" column will have Name A populated in it. Otherwise, it will be populated based on this index match: =INDEX({EMU Contact}, MATCH(Region@row, {Region}, 0)) Looking at some other…

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Popular Tags

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions

Get a Countif on values in a multi-select column of cells
I am trying to write summary formulas to determine how many values appear of each in the drop-down of a column. I tried adding a wildcard, I have tried HAS, and tried CONTAIN, but I'm still not getting the right values. Currently I am getting 0 as a return value on this formula: =COUNTIF([ROI Type]:[ROI Type], HAS([ROI…
summaries in report
Hi i'm trying to build a report with summaries options. i have two column that i want a Min and Max summary. the column for the Min i a texte column and works well. but the other column has formulas and the Max doesn't works. there is a way to fix that? (instead of creating another text column in the source sheets?) here…
Converting email addresses with hyphenated First and/or Last Names
I am trying to convert email addresses into a Name field with the employee's First and Last Name, capitalizing the first character of each part of the name and keeping the hyphen in any part of the name that has one. I started with hyphenated first names so taking shmoe_joe-bob@company.com and converting it into Joe-Bob…

Formulas and Functions

Discussion List

Help Article Resources

Categories

Popular Tags