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 can I use cross sheet reference to combine columns from the source sheet into destination?
I've been trying to use a cross-sheet reference to combine columns from my source sheet into one column on my destination sheet. However, I can not even pull information from one of my cross references. This is the code I've been using. =INDEX({Job Book Job Number}, @row) It keeps giving me #unparseable If I use that…
Function to get last modified date for cell
Hi, I'm looking to add a column that records the last modified date of a given cell/column in a row. I know that you can record this using automated workflows but I would like to avoid this solution as I'd have to update many Smartsheets - is there any way to do this using a column formula?
Status with Predecessor
Hey All, Need help with a formula. What I have works, but I want to add another condition to it though I am not sure how. I do not want the status to show as ready unless the predecessor is complete. I'd like a copy/paste formula vs referencing to a specific row. Thoughts? Here is the current formula =IF(AND([%…
Index/Match - Children
How do you use the index/match formula for a child based on the name of the parent. In the screen shot below. The yellow highlighted area would have the formula looking up who child row is assigned to based on the job # (circled in red).
Separate notifications for multiple Update Request lines
Is there anyway to make it so that the recipient will receive separate notifications even if seven or more rows are triggered at once? I have read in different posts that, "Placeholders will generate separate notifications when using placeholders with Alert or Update Request actions in change-based workflows if six or…
Adding 1 month to date over multiple years - errors
Hello, I have a sheet in which I need a formula to advance a date by 1 month, over multiple years. I have used the below formula, which works to advance within the same year, and move from Dec to Jan, but every month after the new year has somehow changed the day in my date. =IFERROR(DATE(YEAR([Jan '24 RCVD]@row),…
Trying to get an accurate count of checked boxes in a report
hi there, I am trying to get my report to only count boxes checked in each row. instead, it is counting the checked and unchecked boxes, which doesn't translate into my dashboard report. Is there a way to make sure it only counts check boxes for each summary?
Date Formula that ignores holidays, applies workday and returns rows for yesterday
I am working on a dashboard report that returns records for "yesterday" but "yesterday should ignore Saturdays, Sundays and Holidays. I was able to construct a formula (using a helper column) that looks at the audit date and returns a "1" if the audit date is one day in the past and ignores Saturdays and Sundays…
Removing a zero in the third place of a text string if found
I have data that the zero varies where it is located. Some examples would be AA01 or AA01x as well as AA10, AA10x, etc. The first two characters are always letters and I want to return the rest of string without a zero in the third place. So if the first column is AA01x, I want the next column to display AA1x. While if the…
Countifs calculation giving wrong number
I'm trying to make a grid that counts how many tickets were open on any given date. In my grid I have a column for the dates I want to put in and then columns for how many tickets were open on that specific date. The formula I'm using to calculate open tickets on that date is: =COUNTIFS({Ticket List - Created}, <Date@row,…

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

I have the Year and Month on a sheet. How can I have the summary look at both to count?
I would like to create a summary if the month is Sept AND the year is 2024 to count. I have found that I can use the and but keep getting an error message This is the Formula I have in the Summary that is coming up #Unparseable =COUNTIFS([Next Renewal Year]:[Next Renewal Year], "2024") AND(([Next Renewal Month]], "Sept")
Formula Help
Hi All - I am hoping someone might be able to help me. I am terrible at formulas and I cant seem to figure out how to pull what I need on my own so I copied a formula from another sheet we have at our company. The issue is that it returns it multiple times. So the expected result is "four", the formula returns "four"…
Are You Able to Join Two Columns into a Data Shuttle Dynamic Dropdown?
Hi everyone, I'm hoping someone here can help me out with a challenge I'm facing in Smartsheet's Data Shuttle. I've been trying to use expressions to combine two columns into a single dynamic dropdown column in a target sheet, without changing the source sheet. For example, I want to combine Num and Name into a format like…

Formulas and Functions

Discussion List

Help Article Resources

Categories

Popular Tags