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
Sign in and ask it here! The community's got your back.

📈 Wondering if there’s a problem?
Check out the Status Page and subscribe for notifications.

💡 Share your knowledge!
Help others by answering questions.

Formulas and Functions

Stumped by a formula? Ask for help or find examples to see how others use a formula to solve a similar problem.

Formulas for COUNTIF on Fiscal Year
Hi Smarties, I would like to count the number of applicants based on the Stage as below. However, I would like to only count if they fall under this fiscal year (July 2021 to June 2022). My current formula is split to three rows (Taking Successful (R5) as an example) This Year : =COUNTIFS({Created Date}, >TODAY(-365),…
What is wrong with my automated colour column formula?
I am looking to automate the colour “status” for my rows based on the health of the action - a calculation based on the ‘% Complete’ and the ‘Due Date’. The goal is to demonstrate a contextual health status. For example, even if an action is only 10%, if the due date is still 6+ months away, the action is still Green due…
Scoring Formulas
I'm looking to convert a process to smartsheet, but am trying to determine if there is a formula to make the concept work. Currently I have a form which asks a series of questions - each question has a single select option of "yes (description)" "no (description)" and "n/a". For each question, there is a score associated…
IF OR Formula
I’m creating a symbol field with colors red, yellow and green for risk level. But I have two different totals columns (because we’re either looking at two separate sets of factors to assess risk - we will have a score for either one factor or the other). So I want to write a formula that says IF Cell A or Cell B cell is…
Zero Formula
Where do I find the formula to have a cell display nothing when the total becomes the number zero? I have a sheet with zeros in multiple places and it clutters to much.
What is the best way to combine these 2 formulas and simplify it.
=IF(SPECIES@row = "Roses", IF([TYPE OF POT]@row = "LPD", IF(PINK@row = "", IF(RED@row = "", [Type]@row / 72, (PINK@row + RED@row / 4) / 72)))) =IF(SPECIES@row = "Roses", IF([TYPE OF POT]@row = "LPS", IF(PINK@row = "", IF(RED@row = "", [Type]@row / 36, (PINK@row + RED@row / 4) / 36))))
Ways to see total duration in months
I'm looking for a way to see the total duration of a project in months. I've found the NETDAYS formula, but the unit is days, not months. Is there a way around this?
Is there a formula to restrict counting ID once?
In my sheet, I have a column that counts the number of extensions for each request. I would like my sheet summary to show the number of of requests that have been extended once, twice, three times, etc. The number of requests extended once was easy to formulate since the request is only referenced once (=COUNTIF([Number of…
Conditional formatting not working with certain portion of a formula
Hello all. I have a date column that is populated via a formula, referencing other cells within the same sheet (both date and drop downs). Before I changed this column to populate via a formula, I had simple conditional formatting based on if date was in the past (red) or in the future (green). Since I added the formula,…
Nested and/or formula
I am trying to set an at risk flag when a date is in the past and the status is not cancelled or complete. I have used the following formula in Excel and it works, however, I cannot make it work in Smartsheet. Any advice? =IF(AND([Due Date]@row < TODAY(), OR(Status@row="Cancelled", Status@row="Complete")), 0, 1)) I did get…

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Trending in Formulas and Functions

Combining selected cells into one cell ignoring blanks?
Hello all, I am trying to combine cell into one cell and I have found a couple other community posts covering this issue. The only problem is that their solution only applies if you are trying to collect the entire row, I need specific row. Originally, I did this just by using JOIN and then selecting each cell, separated…
Using Parent Rows in Reference Another Sheet Formula
I'm attempting to calculate the total number of pediatric calls by county using this formula all referencing another sheet: =SUMIF(PARENT({MAHEC County}, "Buncombe"), {Pediatric Calls}) However, it is not working. Any suggestions?
Is there any way to pull in Date AND time to a column when a specific condition is met?
I've been trying to create a column that populates the date AND time when a condition from a different column is met. Is there a way to do this without creating any other sheets or extensive work? It seems like a very simple thing to do as they have a record a date workflow but it doesn't include time. Don't know how this…

Formulas and Functions

Help Article Resources

Categories

Popular Tags

Formulas and Functions

Discussion List

Help Article Resources

Categories

Popular Tags