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.

Calculating costs for a task over more than one month
I'm trying to come up with a formula to calculate the labor costs per month in one sheet ( 12 columns, one for each month), by referencing another sheet, and referring to the start/finish columns of said task. E.g. Task 1: Start = Aug 27, Finish = Sept 5. So in my cost sheet, the formula needs to refer to the start date…
Using IFS statement with date and check box
The simple statement is I want to have a sum of work hours in a department, that is still incomplete, during a calendar period. Currently the formula is: =SUMIFS([Work Hours]:[Work Hours], Department:Department, "Wire", Finish:Finish, >=DATE(2020, 9, 1), Finish:Finish, <=DATE(2020, 9, 30)) * 8 A suggested version has been…
Same formula on two different sheets one works the other does not
I copied a working formula from one sheet to another sheet and changed a column setting. I cannot figure out why the column name in the formula doesn't "active". Any ideas what is wrong?
Why does this return invalid data type? Any suggestions?
I'm trying to get a rolling 30 day issue count to return the 5th top number so I can give a list of clients that have the top 5 highest volumes of issues in the last 30 days. =IF([Rolling 30]:[Rolling 30], 1, LARGE([Issue Count]:[Issue Count], 5)) My Rolling 30 column is a checkbox with a formula in it. =IF(TODAY(-30) <…
With COUNTM I can determine multiple selections are made, how can I extract the specifc selections?
If I have a multiple selection column, like 'assigned to', where I can select some or all of the people, can I determine which specific people from the list have been selected (without using FIND or CONTAINS and looking for names one at time?
Divide by Zero Error
I'm deep over my head in a couple formulas. In the screen grab below you'll see Location and the various formulas that follow: Count based on sheet: =COUNTIF({ERG MEMBERSHIP EA Location}, @cell = "REMOTE") Previous Month - count based on sheet: =COUNTIFS({Joined Date}, IFERROR(AND(IFERROR(MONTH(@cell), 0) = MONTH(TODAY())…
Conditional Formatting to ONLY Child Row
Hi, I have applied conditional formatting to my project sheet to change rows to a specific color based on their status. However, I want it to ONLY apply to the child rows. Is there a way make it so my parent rows do not change color?
Contains & "Invalid Column Value")
Hi I am trying to use this formula: =CONTAINS("Medium"; [Rank]:[Rank]) Rank column is the column with drop-down (single select) where user can select one of these: High (Business Critical) Medium (Moderate Need) Low (Nice To Have) However, it results in the "Invalid Column Value" error. Do you know how to solve this? thank…
=if(or(isblank( issue
Viewed a number of threads and cannot seem to understand why I am getting unparseable? =IF(OR(ISBLANK(Finish1), Start1 < Finish1, "OK", "Start>Finish")) Help appreciated.
How do I write a formula that only finds unique or duplicate text from a single column?
I have a sheet with 7,195 rows of data. I'm trying to write a formula that looks in the "Full Name" column to find which names are unique and which are duplicates. Any suggestions? I've tried this formula: =IF(Countif([Full Name]1:[Full Name]7195, 1) I also thought using the DISTINCT function may help, but I'm not sure.

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