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.
Discussion List
Mirror Data in Smartsheets
Is there a way to automatically mirror data between 2 smartsheets. Something similar to Data Shuttle between excel and a smartsheet?
Complex Index and Match
Really banging my head against this one... I thought maybe the issue was that you can't use index and match with columns populated by formulas because it may not be the same type of content (IE text or number)... so I tried adding helper columns to convert to text = "'" + (previous coulumn @row) no dice Tried using…
Need help with Conditional formatting
Hello Experts, Need your help with one issue which I am trying to resolve, I have a data in which I applied the Conditional formatting based on the helper column as below - 1) If Current Coverage CN (M) = Previous Coverage CN (M) it will result N 2) If Current Coverage CN (M) > Previous Coverage CN (M) it will result T 3)…
Repeat cell until new value is reached
I need a column formula that will repeat cell contents until a new value is reached, then repeat those cell contents until a new value is reached, and on and on. I want it to look like it does in this example for Column19 where the department is repeated until there is a new listing. Any help is appreciated!
Referencing multiple sheets
Hello, I currently am looking for a solution to the below sheets, any help would be appreciated! I have an assignment sheet that has a ticket number column, batch column and assignee column. Each person has their own sheet to update the result (hidden column also shows the batch number). I am trying to find a way to build…
Task@Risk
Hi. I'm trying to create the following formula for a RAID Log Task@Risk, to display a Red Flag if criteria is met: If [End Date] is blank, then "Update Date", If the STATUS chosen from the STATUS drop-down is "Not Started" or "In Progress" or "Cancelled" or "On Hold" or "Delayed" & [End Date] has passed Today's date, then…
COUNTIFS between dates
I am trying to manage resources for my projects. I have an assigned owner column and an additional 10 columns to capture individual project participants names, per task. I am trying to calculate the number of tasks assigned to each of them, by month. For example: how many tasks are assigned to Paul for Dec 2022. I can get…
Report Filtering
Hello I am working on adding some information to a dashboard. I want to show locations that are closed on certain days. I created a report called Center Closed hours and included the correct columns from the main sheet. I set up the filter to say if Monday Open is equal to CLOSED, Monday Closed is equal to CLOSED etc. The…
Count one site pending circuit
I need a formula to count how many sites still have one circuit to be completed There are two columns "1st Circuit Status and 2nd Circuit Status" If one cell has "Circuit Delivered" and another cell has "anything else beside "Circuit delivered or a blank cell" counts one site If two cells have "Circuit Delivered" they…
RANKEQ showing multiple 1 Ranks but still showing a rank of 2
I'm running into an issue using RANKEQ to determine the top 10 categories with some criteria while working with census data. The formula I'm using is: =IFERROR(RANKEQ([Speak English less than "Very Well"]@row, COLLECT([Speak English less than "Very Well"]:[Speak English less than "Very Well"], County:County, =County@row,…
Help Article Resources
Trending in Formulas and Functions
COUNTIFS formula for any future date?
Hi all, I am trying to create a COUNTIFS formula that counts if the due date column on another sheet is today or any future date. I tried =COUNTIFS({due date column's range}, >=TODAY()), but it is not working. Any advice would be appreciated! Thank you.
Sheet Summary Field - Count Value from Dropdown
What's wrong with this Formula? Getting an Invalid Ref
I am trying to get a unit count between a date range.
I am trying to get a unit count between a date range. I have two difference columns from one sheet and two difference columns on my main sheet I am referencing. I keep getting unparseable. Is there a way to to get the number I want with using the countifs formula? Or should I be using a different formula? This is the…