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
formula for date
I have a column titled "order submit date" i would like for the formula to put a checkmark or flag (which ever is easier) in the "process time" column if its been 5 days since the "order submit date entry" do i have the formula sort of correct? =IF([Order Submit Date]@row >= TODAY(5), not sure what goes here to return aβ¦
VLOOKUP example provided in documentation does not appear to be correct?
Smartsheet VLOOKUP example does not seem to be correct. In the example VLOOKUP Function | Smartsheet Learning Center, unless I'm missing something, the first formula example "IF([In Stock?]1 = 1 (true), VLOOKUP(βT-Shirtβ, [Clothing Item]1:Status3, 4))" defines the table starting "Clothing" column row 1 to "Status" row 3.β¦
INVALID DATA TYPE error when using MONTH()
Hi, I'm working on a formula that will use a date to reference a value in another sheet: The sheet that will be referenced looks like this: I'm planning on using INDEX and MATCH to get a date value from a row in the first sheet to return a percentage value (based on the month that row occurs in) from this sheet. Theβ¦
%Complete
I am looking for a formula that will calculate % Complete based on two dates. I have been successful at getting this formula to work on projects that have already began but completed dates or not started dates are giving me a percentage that is negative or above 100. I would like projects that have not started to say "0"β¦
Automation - Sheet Update based on Criteria
Hello again Like a typical N00B I'm having wonderful ideas with almost no knowledge of the product (go easy, I'm just rolling into my 5th hour in 2 days of using it) I wanted to know if anyone has any experience or thoughts on how best to approach this scenario as I've found some challenges with my current workflowβ¦
Formula to evaluate child statuses to determine the parent status
I have different milestones in a project and want the parent milestone level to show a general "temp" based on the statuses in the child tasks below it. The thought is, if any of the child tasks contain a status of "delayed, on hold, blocked, requires update" the status cell on the parent Milestone row should turn yellow.β¦
Count Criteria But Not Blanks
Hello! I am trying to count if a number is less than 45 but leave out the blanks. I have the first part of the formula working, just having trouble with the "do not count if blank" part. Can anyone help? =IF([Days to Complete Calc]@row < 45, 1) Thank you!
Exceeding 100% Completion
How do I override the system to account for more than 100% Completion. For instance, the initial project accounted for 4 visits; and when the project was completed the client wanted an additional event. When I add that event the Completion still adds up to 100% and not to 125% to show that the project went over scope.
TRIMMEAN
How does Smartsheet calculate TRIMMEAD (Excel)? Calculating the mean (AVG) taken by excluding a percentage of data points from the top and bottom tails of a data set. Thank you so much.
Count of Issues by Month/Year
Hi All I want to add to my summary sheet a count of instances by month and I'm not sure what I'm doing (only 4 hours into using it!) My source sheet has the data formatted UK (dd/mm/yy) and I want to have the summary sheet show mm/yy with the count of instances that occurred within the respective month and year Anyβ¦
Help Article Resources
Trending in Formulas and Functions
Creating a multi-sheet formula to match a contact list that references a drop-down list
Hello to you, Smartsheet community! I am in need of assistance with formulas. I'm so lost in the syntax that I don't know right from wrong, up from down, HAS or COLLECT π΅. I do not identify as a power user with formulas β wizards are my dearest friend β so this push into the advanced features has been a journey. Is itβ¦
Conditional Formatting for Dates
I have two columns, one with a projected date and one with an actual date. I want the actual date to turn red whenever it is later than the projected date. How would I make this happen?
IF formula from Excel to Smartsheet
Hi, I currently have a spreadsheet in Excel I am moving to Smartsheet with an IF formula. Can someone help translate what this formula should be in Smartsheet? In Excel, the Current Widget Liabilities formula is: =IF((2023 Paid Widget Balance + 2024 Paid Widgets - Widgets Created in 2024) <0,0,(2023 Paid Widget Balance +β¦