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
Schedule Health Based off Duration and % Complete
Hi - I'm trying to write a task health formula with the following criteria and am hoping someone can help. Here's what I'd like it to calculate: GREEN: If today is less than 50% of the task duration and % complete is more than 50% YELLOW: If today is more than 50% of the task duration and % complete is less than 50% RED:…
Hyperlink function
Based on community articles, I am confused if the Hyperlink function exists or not. Regardless, here is what I am trying to accomplish. In the roadmap tracking sheet, I have a column "Assigned Team". Each team has a workspace with top level workstream tracking (and intake process) and folders per project. What I want to…
Return a date if the date falls between two dates
I have two sheets - Sheet 1 - this includes "Facilities Cycle start date" and "Facilities Due date (EOD)" Sheet 2 - includes "Request date Simplified" I need to write a formula in Sheet 2 to get me the "Facilities Due date (EOD)" from Sheet 1 if "Request date Simplified" lies between "Facilities Cycle start date" and…
Autofill a date based on the date in another cell
In the grid format I would like to be able to have a field auto-fill with a date that is based on the date in another field. So I'm doing conference tasks - if the conference starts on May 15th I enter that in the start date and have other fields automatically fill in for tasks that need to happen prior to the conference.…
If function with dates
Hi Experts, I'm trying to create a formula which should give me a Date as result. I have 2 check boxes and depends what it's clicked I would like to get the date below as results. If HALO Calendar is not clicked than DATE DATE(2022 / 8 / 26) IF HALO Calendar and Virtual Model CR1 is clicked than DATE(2022 / 10 / 13) If…
IF AND Formula Help
Hi everyone! I am hoping you can please help me. I have tried IF AND, IF OR, and IF CONTAINS formulas but nothing works. Referencing the below screenshot, I have a checkbox in the column titled "Carry Forward". I want to have the check box checked when the column titled "Vacancy Status" contains either the word "Assigned"…
Data Calculation For Digital Marketing
So i wanted to give an individual person networth and other awards some point and wanted to have separate sheet for overall score. Danny Duccan Net Worth 5.5 Million Dollar (5 points) Danny Duccan Subscriber (3 points) Danny Duccan video views 70 Million (4 points) now take 40% from first and 20% from second and 40% from…
How to Group/Summarize Reports with Row Limitations?
Hello, I have created a report to combine two project sheets together, so I can verify the project schedule. However, when I go to either group or summarize the data I get the following message and then all the data disappears. How can I work around this issue? Thank you, Sheri
How do I sort by due date in a report?
Is there a way that once I have grouped in the primary data in a report I could sort by due date in that report? I have grouped the primary column and then summarized the start and due dates, but would then like to sort by the due date so the "Project-Interviewee#" and "Interview Name" is sorted in descending order by the…
How to limit dropdown list values to only be selected once per sheet
I am trying to figure out if it is possible to create a dropdown list and then enforce that each of the potential values could only be selected once per sheet. For example: I have a sheet with two columns. Project Name and Project Priority. Project Priority is a dropdown list with preset values. If I select the Project…
Help Article Resources
Trending in Formulas and Functions
Flagging Duplicates with Multiple Criteria
Hi all, Trying to identify when I get a duplicate that meets both criteria, when the Work Order Number is the same and when the Service Area is the same. I'm using the following formula, =IF(COUNTIFS([Pass Number]:[Pass Number],[Pass Number]@row,Site:Site,Site@row)>1,"Duplicate","Not Duplicate") But its not working…
Percentages as progress bars
Hi we are using the below formula to calculate a percentage which is then reflected by a progress bar, when the overall percentage is at 49% the progress bar is showing as "full" instead of reflecting the criteria of the formula underneath =IF([Overall %]@row < 0.25, "Empty", IF(AND([Overall %]@row >= 0.25, [Overall %]@row…
Hello! How can I work an OR statement into this formula? I need it to look at 6 different columns.
=IFERROR(INDEX(COLLECT({Provider being reviewed}, {MRN 1}, CONTAINS(@cell, MRN@row)), 1), "")