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
Creating a schedule
I have 2 sheets. One with a list of a couple thousand accounts and unique priority assignments, and another with a list of dates, and how many can be assigned to each date. What is the best way to popular a schedule of accounts for the given date? I have tried multiple ways, and I feel like they are all extremely complex,…
Using Countifs to Check for Multiple and Exact Conditions
Greetings! Reaching out to this group for assistance in creating a formula that counts the number of times a certain Product is associated with a given Account ID. The challenge is that the Account IDs may include the same characters but differ based on the case of a letter (a cap or small "a" at the end of the IDs shown…
Summary Field return symbol for most recent entry
Hi, I've been searching and testing without much luck. I have a task list that the project health is updated with each task. I added a column for last modified date. I would like to have the summary field reflect the health on the row where the most recent modified date is. This summary field will be collected in a summary…
Largest Gap Between Dates?
Hi . I have a column which contain dates. Is there a formula that would: Return the largest gap between the dates in the column? Return the dates? My column contains dates of call logs. I want to see the largest gap that occurred (for example, we didn't call anyone for 7 days). I also want to see the two dates used to…
MIn and Collect fuction not returning value
=MIN(COLLECT({RAW NOC INC Data Range 1}, {RAW NOC INC Data Range 5}, "Outage", {RAW NOC INC Data Range 6}, "Mountain States")) I am using the above formula and it is returning a 0 and not the oldest update time. When i change it to date as the format i get nothing but a blank spot. Catch is i want the date and the time…
Can a milestone task be closed based on completion of an approval task?
In each of my parent group of tasks I have an approval task, followed by a milestone task. Both are important for reporting. Example: Task1: Manager approves ABC Webpage for publishing Task2: Milestone: ABC Webpage ready for publishing - Predecessor for Task2 is Task1. Problem: When Task1 is marked Complete, I want Task2…
How do I auto-update Start Dates for tasks that are incomplete?
I want the vertical line representing Today to "snowplow" the End Date of every overdue task forward, automatically adjusting all Incomplete tasks and their dependencies so that the Finish Line for the entire project automatically adjusts itself until all tasks are complete. Here is the automation I am TRYING to create,…
Need help creating a Week Number in a month formula for a full year
I am creating a weekly coaching % dashboard and need to create a formula that will display data by weeks in each month for a full year. How would I do that is it begins January 1 2024?
Hello I need help with creating a formula.
So basically I need to calculate the sum of scores but the total depends on 29 (from the image below) so if 29 is W, the sum is added excluding 28 and if 29 is C then we exclude 27. Does anyone know if I can possibly have excel detect the change of row 29 and based on it, it exclude and calculate the right range?
How to display on the same row latest comments and previous comments?
Is there a way to display latest comments and previous comments made on the same row/tasks?
Help Article Resources
Trending in Formulas and Functions
Why is 'DAY' Value from a Date being populated incorrectly?
I have a 'Formatted Date' column with a column formula (below) that formats the date from the row's date timestamp, which I renamed to 'Training Completion Date', into DD-MMM-YYYY format. All rows but one is getting the correct day from the DATE. I cannot figure out why 10/08/24 returns 9-Oct-2024. Curious to know if…
Counting items in a cell separated by comma
Hello, Thank you in advance. Need help getting the count of the values in a cell that are separated by a comma. Example in a Cell I could have three values: Blue, Green, Yellow. Need to return the value 3 but I can't seem to find that anywhere in the community. Thank you again!
Need a formula for % complete for all phases on the parent project summary row. Row is non editable
I need an average formula in the parent project summary (row 23). Formula should include all phases (initiating, planning, executing, closing). The parent row is un-editable and not allowing me to enter a formula.