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.

VLookup or not
Im working on a scrum dashboard and want to show the actual sprint name. The Sprint Name (e.g. Sprint 01, Sprint 02...) is in the primary column at Level 2 (task at Level 3 and Release at Level 1). I have a column with the status ("Do", "Doing", "Done") and only one Sprint can be in the "Doing" status. On my metrics sheet…
workday calculation with if conditions
Hi, I'm trying to add a workday function to my current calculation. I already use if condition in the cell. Here's what I have. If location is Riverside, then add 3 days to ETA. If location is Memphis, then add 5 days to ETA. IFERROR(IF(Location1 = "RIVERSIDE", ETA1 + 3, IF(Location1 = "MEMPHIS", ETA1 + 5)), 0) However, I…
Strip Time When Linking Max Modified (Date) Cell to Column with Restrict to Dates
Hi Smart Sheet Community! I am applying a formula to a cell in our project schedule sheets that I found in a couple posts to grab the MAX value of the Modified (Date) column: =MAX(Modified:Modified) + "" My colleagues and I will be linking the cell with this formula from our project schedule sheets to a "Last Updated"…
SUMIF
Hi, I'd like a formula to calculate the below SUM the leave days Request(Column Number of Days) against the employee(Name Column) if Approved(Manager Column) and Approved(EXCO Column). If either one of them are not approved then don't SUM the leave days. This is what i have so far but cant seem to get it to work.…
Count checkbox and text in a column
Hi, is it possible to count in a column, if I have check box and also N/A ? I need to count every cell that has a check in the box or the N/A. Thank you !
Need Formula to auto populate todays date when box checked
Please share the formula that will auto populate the current date or "Todays Date" in the Completion Date column when the checkbox is populated in the "Done" field.
Adding Positive Parents
Hello all, I am trying to set a formula up that adds the sum of several Parent lines, but only if they are positive. Adding all parents under one banner i use the Sum(Children) however, I tried to add only positive parents by using a SumIf(Cell1:Cell5, >0, Cell1:Cell5) but in doing so only managed to add all positive…
SUMIF condition
Hi guys! I have to calculate average in my sheet and I will use: SUMIF / COUNTIF - COUNTIF formula is working. - SUMIF formula is not working, i am getting #INVALID OPERATION The SUMIF formula is (screenshot attached) =SUMIF([Assigned to in Region]8:[Assigned to in Region]136, "Sarah Mabiletsa", [Quote Prep Cycle…
LOOKUP Formula based on result between date range
Hello, I've tried to create a LOOKUP formula that will provide the "Approved Value" if the "Date Certified" calls between the period stipulated in "Period Start" and "Period End," without much success. Based on the screen-grab, only two values should be returned if the formula is successful. (I've manually entered these to…
Nested IF and
I trying to use to create a sheet that I will use to determine the status by comparing 2 status and then give an answer the e.g. If the status1 is low and status 2 is low the actual status should be low, I used the following IF/AND Statement and it worked fine: =IF(AND([Status1]3 = "Low"; [Status2]3 = "Low"); "Low"; " ")…

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

Index/Collect while meeting specific criteria and doing math
Good afternoon community, I'm in need of help with a formula that I feel has come close, but still just off of the mark. I have three sheets: The first is holding static data (Inventory Cost sheet). The second sheet (Tool Inventory) is where data comes into the sheet from a form and some math formulas do calculations based…
Issue with Highlighting Columns that have different values
I have a sheet where I am tracking values from one month to the next (values imported using DataShuttle). I want to highlight the columns where the values are different, using a helper column: =IF(Bananas@row = [Previous Bananas]@row, 1, 0) However, I am having the formulas return 0s when the formulas are NOT DIFFERENT.…
Net Days between a previous day and today.
I have the following columns Comments Date of Last Attendance Weeks Not Submitted Under "Comments", you might see something like "Last Attendance Day: 11/01/2024." I used =RIGHT(Comments@row, 10) to get the Date of Last Attendance. I need to determine how many days it has been since that date using today's date, and divide…

Formulas and Functions

Discussion List

Help Article Resources

Categories

Popular Tags