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 do I pull resource capacity into a grid?
I need to be able to manipulate the capacity data further. How do I pull this through into a grid to analyse?
Auto Flag Risk on Project Sheet if a risk is opened in Risk Register
Hello All, I am new to Smartsheet and struggling to make a cell behalf the way I want. To give some background: I have a Project Sheet with flag symbol for risk and a Risk Register. When I add a Risk to the Risk Register, I want to automatically Flag the Risk Flag on the Project Sheet row that relates to it. ie on Row # 1…
COUNTIFS with MONTH
Hi all, I am trying to create a formula in my sheet summary that calculates total number of tests passed for a given month. I have already created syntax for "Total Samples Passed Today", but the month criteria keeps coming back as an error when I add it in. :( Total samples passed today looks like this: =COUNTIFS([Retest…
Map values to date buckets
Hi I have the following table: Sprint, Start date, End date 20.01, 10/01/20, 10/15/20 20.02, 10/15/20, 10/30/20 20.03, 11/01/20, 11/15/20 I want to show the Sprint value, whenever a user enters a date between the dates that are next to the name. For example, show 20.01 when a user enters a date from 10/01 - 10/15. How is…
Importing from excel to smartsheet project plan template
Hi there, I have created a project plan template that I would like to use. I have the project plan and data set in an excel that I would look to import. When I import the data - my duration, predecessor columns are not working as expected. It also does not provide me the functionality to amend columns that were brought in…
Ignore blank Cells
Hi I'm trying to get the following formula to ignore blank cells =INDEX(([1]:[1]), MATCH(MAX(Created:Created), Created:Created, 0)) Can't quite get it to work.
Changing Status Based on Another Sheet
Is there a way to link a cell from sheet A to respond to a change in Sheet B? For example, if I change a cell in sheet B to "under review by editor" could I change a corresponding cell in sheet A to "In Edits"
Identify row number
Hi, I need to calculate the cumulative of expenses in my sheet, I used the below formula: =SUM($[Expenses]$1:[expenses]@row) However when I sort the sheet, and row 1 for example become row 12, the formula will update itself to: =SUM($[Expenses]$12:[expenses]@row) Is there a way to fix this so the formula is not affected by…
How to add Dates to formula using TODAY(+7), etc.?
So I have several formulas calculating what date range a date is in...two weeks out, three weeks out, etc. =COUNTIFS([Calc if Done]@row, =0, [End Date]@row, AND(@cell > TODAY(+7), @cell <= TODAY(+14))) This give me a 1 or 0 result so I can count them, great! How can I change the formula so that I know what the DATES are…
Counting Rows with multiple contact columns
I need to know how to count a row if a specific user is in ANY of 3 contact columns in a specific row with the possibility of multiple users - HELP😫 =COUNTIF(OR({ProgMgr}, CONTAINS("Bob", @cell), {ProjMgr}, CONTAINS("bob", @cell), {ChMgr}, CONTAINS("bob", @cell))

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

Using JOIN/COLLECT and an OR?
I'm trying to write a formula using the JOIN and COLLECT options. However, I've hit a sticky point when I want to include an OR criterion on the COLLECT portion. My current formula (which works perfectly) is: =JOIN(COLLECT({Assets Range 1}, {Assets Range 2}, [Tool]@row, {Assets Range 3}, "Live"), ", ") However, I want to…
INDEX/COLLECT with both MATCH and CONTAINS criteria
I am looking for help with a cross-sheet INDEX/COLLECT formula. I need to MATCH in one criteria and CONTAINS in another criteria. I can get them working separately but I don't know how to combine into a working formula. Source Sheet: Destination Sheet: In the Attending School column in Destination Sheet, I'd like to…
Parent Color Symbol, Based on "Average" of Child Symbols
Okay, here I am again! On the Project I am working on, the Stakeholder wants parent rows for no reason other than to be able to expand and collapse the data. I nicely suggested that since the data would only be for 1 year (broken down in quarters and about 5 users listed each quarter), that since the data was not excessive…

Formulas and Functions

Discussion List

Help Article Resources

Categories

Popular Tags