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.

Status - Red, Yellow, Blue
I haven't done formulas in a while but can someone help me out with the following. I attached a screen shot below. If %Complete is 100% then status would be blue If end date is less than 10 days away then status would be yellow If end date is on or past end date then status would be red. Thanks
Referring to Adjacent Row
I am using the following formula in Color column to shade every other row in a sheet: ROW 1: No formula ROW 2 = IF(Color1 = 0, 1, 0) ROW 3 = IF(Color2 = 0, 1, 0) I have over 1,000 rows. This works fine. However, if I insert a new row, I have to drag the formula from the row above all the way down to the last row the fix…
Formula to Create Unique ID on Specific Rows
Hi Everyone, I am trying to create a unique ID but only on specific rows, so the auto-number column type is not suitable. I would like the ID to be sequential. I started by generating a number, 1, where Course and Action Due Date are blank. I have successfully done this by using the formula: =IF(AND(ISBLANK(Course491),…
COUNTIFS to include cells with text
I need to create a cross-sheet formula that counts the number of entries for a month and if there is text in another column. I've come up with the formula below, but I get an Incorrect Argument error: =COUNTIFS({Review Month}, =1, ISTEXT({Critical 1})) Where am I wrong here?
From X to End of column
My sheets have a "header" that serves as a navigation and also serves for the metrics. My problem is that I would like to have the range of a function encompass the entire column (open-end) but i want to be excluding the aforementioned header rows. I tried thing such as [Column Name]21:[Column Name] in an attempt to splice…
PLEASE tell me what is wrong with this formula
=IF(Status8 = "Complete", "Blue", IF([Due Date]8 < Today8, "Red", IF([Due Date]8 = Today8, "Yellow", IF([Due Date]8 > Today8, "Green")))) Very new at this, but I can't see the problem...
Complex Formula for RYG Automation
I automated the RYG circles according to several columns of a Smartsheet grid for project management purposes. The logic is below: IF [Proposed Product Launch Date] <TODAY, "Gray" IF [Proposed Product Launch Date] >=TODAY AND [Deviation from Proposed Launch Date] <100, "Green" IF [Proposed Product Launch Date]…
SUMIFS trouble
I feel like I'm almost there with this, but just can't figure it out. I'm trying to determine a Sales Rate (money/hours) per Estimator, per month (date range). My column headings are: Ops Start Date, Estimator, Scope Hr, and Contract Amount. I think the problem is using a date range within a SUMIFS formula. I know its…
Month Function
Hello, I am trying to figure out how to extract months out of a grid. My goal is to count how many assignments were completed within each month of the year. e.g. | January | 45 | --------------------- | February| 20 | However, I am finding trouble actually calculating the amount based off of the creation date. I figured a…
Dynamic Sum from Multiple Sheets in a Workspace
Hello, I have a workspace full of our active projects. Each tasks has a status (not started, in progress, complete) and I would like be able to see the total numbers of these tasks statuses across all the projects. I already have columns that count the number of each task status in the individual projects, but I would like…

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