Formulas and Functions

Archives for August 2023

Enabling Information Push and Pull in Smartsheet
I need guidance on effectively using Smartsheet to achieve a particular data interaction: the push and pull of information. Here's a snapshot of my scenario: I have a master sheet with approximately 500 rows and 90 columns. I need to distribute specific column-based data subsets to 20 different personas. Each persona…
I CAN´T SET MULTIPLE COLUMNS FORMULA
=IF(AND([Fecha de analisis]@row = ""; [Fecha de analisis]@row = ""; "FALTA COMPLETAR"; "COMPLETA")) tHE MESSAGE IS: THERE IS AN ERROR HERE AND I DONT KNOW HOW RESOLVE IT
Combine all row comments in one cell in same row?
I am needing to create a cell that has all of the comments for a particular row combined. We need to display more than just the latest comment. I understand that there is a 4000 character limit for a cell. It would be nice if it showed the latest 4000 characters or we can add a second row/ cell for overflow. I found a post…
Formula to auto-summarise Delivery Risk assessment
Hi all, I'm attempting to build a function into my master milestone tracking sheet which will auto-populate my ‘Delivery Risk’ column, based on a series of conditions met in other cells in the row. Underpinning thinking is as follows: ‘Target Milestone Date’ are set for every row Assumption that ‘Actual Completion Date’…
Calculate age from a birthday date
Hello, We receive the birthday date from a form and I want to put on cell the age automatically. How can we do to make a formula to put the age form a birthday date. I think this works. =(TODAY()-(birthday))365 Thanks for your help Johann
setting up a conditional deroulant menu
Hello every ones, I'm using smartsheet for a professional project and I've got a size problem: I've got n "source" file; the inventory, in the inventory, each machine has its own tag, an equipment class (selectable from a list of equipment classes in a drop-down menu). There's also a "logbook" sheet where people log in to…
Traffic light formula based on date and % complete
Hi, I'm wondering if someone is able to help adjust this formula slightly to include the following information, I just can't seem to get it right: Current formula: =IF([% Complete]@row = 1, "Gray", IF([Start Date]@row > TODAY(), "Green", IF(AND([% Complete]@row < 1, [End Date]@row <= TODAY(+2)), "Red", IF(AND([%…
Countifs statements for date fields that are in the past
Hi there, I am looking to summarize data from a master sheet for inclusion into a dashboard via a project metrics sheet. Within the master sheet I have an “Opening Date” that is populated when the opening date is decided. I want to populate the Dashboard with the number of sites that HAVE opened each month (past tense).…
Formula to generate date 1 year from today?
I am trying to figure out a formula that will generate a date that is 1 year from today. For example, if we have a contract that requires 12 months of notice to cancel, and I have a column to track the Earliest Possible Termination Date, I want that column to tell me the date that is 1 year out, or 365 days after today.
COUNTIF Formula to count multiple values from Column in another sheet
I've successfully set up COUNTIF formulas to reference a single column from another sheet when looking for only one value. I'm now trying to set up a formula to look at a column in another sheet and count up multiple values for a total. The column is single-select. Status (Name of the column in the sheet I'm referencing)…

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

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