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.

Formula to calculate total amount due in previous month
Hi all, I'm trying to create a formula that will achieve the following: Sum up the total # of days that fall between two dates within the last previous month Date 1: Admit date Date 2: Discharge date For example, Patient A was admitted on 6/1/24, and discharged on 7/15/24. I'd like to know how many of those patient days…
Sheet Row IDs
Is there a way to customize the Row ID such that the prefix can be dependent on the date? For example, set prefix to "YYYY - " which means "2024 -" since the creation date is in 2024.
Count or Sum by row
I am working on a R, Y, G system (or 1, 2, 3 depending on which formula makes the most sense) by employee… but, I am struggling to figure out how to count the number of reds by row or to sum amounts in multiple columns within the row for some reason. For example, I have values/colors assigned for tenure and pay range…
Link to a row in a different sheet based on a particular criteria
I have a Gantt Chart with information for a number of projects. I would like to link the the task name, duration, start & finish date, % complete and status of my top 10 projects to another separate summary sheet. I have a column Project# which identifies each project with a number i.e 1,2, 3 etc Can I pull through all the…
How can I reflect due dates my person from a reference sheet, showing if tasks are due or overdue?
I need help reflecting data from a reference sheet column to another column in a different sheet, specifically to the display due dates by person. I want the columns (I have one for "Due" and "Not Due") to show whether each task is due or not, I want to use this information to create a dashboard with a graph that…
Aggregated Ticket ID
I'm still getting error as #No Match for this formula, not sure why. I was able to use the same format to my other sheet and it worked, however on my other sheet, i just changed the range but now it's not working. I made sure column names are correct as stated on the formula. =JOIN(COLLECT([Ticket ID]:[Ticket ID],…
Formula Help
I am trying to use an IF contains formula to look at the assignee column and the notified column for two different names and continue to get #unparseable. Does anyone have insight or the correct formula?
Service Contract Status Formula
Hi there, I'm trying to figure out a formula to return a Green/Yellow/Red Symbol based on a calculation of the date an item was purchased in comparison to the length of the service contract. I am able to get the "Green Symbol" using this formula: =IF([Service Contract Expiration]76 > TODAY(), "Green") But, I'd like to have…
Status Column Formula
What I need: If a date is entered into the [HR Review Date] change the [Status] to "Pending HR Review" If the date entered in the [More Info Requested Date] greater than or equal to the [HR Review Date] change the status to "More Info Needed" and or if the date entered in the [HR Determination Date] is greater than or…
Formula Question
Hello, I have a scenario where I want to build in an If AND formula and just am not too familiar with how to set it up. I want to set it up as follow: If someone's age is >= 55 AND their tenure is >=10, I want the column to state yes. If it does not meet this criteria, I would like it to say No. Could someone help me set…

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

NEED TO ADD DAYS TO THIS FORMULA IF ANOTHER CELL HAS SPECIFIC ITEMS OF A DROP DOWN.
I have this formula that works for this cell when information is missing from reference cell A. However now we want to be able to add additional days if reference cell B has specific items checked in a dropdown. I just can't seem to get it to work. Any suggestions. =IF(ISBLANK([ ENGINEER (tentative start date)]@row), "",…
Help with Average Formula
Hi there - I'd like to ask for a hand to calculate an average formula. This is a cross sheet formula where I want to aggregate an average time duration by Assigned To, by quarter. (I also have instances where my time duration is zero - I think that was causing me a divide by zero error). Metrics sheet/destination: Source…
Check a Box if Between Dates on Another Sheet
Hey all, I am trying to build a formula to check off a box on my sheet if at least one date is found between two dates on a separate sheet. I've tried a million variations but here's what I have so far, thinking I was overcomplicating things =COUNTIFS({Approve Date}, >=[Week Starts]@row, {Approve Date}, <=[Week Ends]@row,…

Formulas and Functions

Discussion List

Help Article Resources

Categories

Popular Tags