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.

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

Calculating the difference between Estimated Duration (Part of the Project Settings) and Actual.
Hi , Estimated Duration is part of the Project settings and drives the start and finish date. I want to calculate the difference between estimate and actual . We want to track in hours. i.e .25h = 15 min. The only way I can get the calculation to work is .25 days but that is really 2 hours, not 15 min. Estimated Duration -…
Summary fields - adding one column and subtracting a second column
Hi All, I have been trying to get a simple formula to work and I am having issues. Maybe it is just my brain but would appreciate some help. I have added and removed SUM after the = and neither work =([VENDOR QUESTION Amount of Credit]:[VENDOR QUESTION Amount of Credit]) - ([AP QUESTION Amt $ Posted]:[AP QUESTION Amt $…
Average turnaround time in 45 day period
Hi all, I am trying to calculate the average turn around time in a sheet summary (=ROUND(AVG([Turnaround Time]:[Turnaround Time]))) for the past 45 days (not all time, like it is currently). I tried adding <=TODAY (-45), but that did not return an accurate count. I feel like I'm missing something simple! Turnaround time is…
How do I find my post
I posted a a question for the community group for a formula and not sure how to find a response.
Vlookup Output doesn't appear in report
I have a sheet that references another sheet to complete the fields via vlookup. However, when a run a report on the sheet, the information populated from the vlookup appears blank? How do I get this information to display in my report? I have a series of complex sheets that need to be tied together without me having to…
show only date vs date time using Concatenate formula
I am using the formula below to display 2 dates in a single cell but its displaying "date time - date time" vs just "date - date". In the original cells only the date displays (no time). Any suggestions on how to set the formula? =[Start Date]49 + "-" + [Actual End Date]49 currently shows 10/6/2020 8:00 AM-10/08/2020 4:59…
Sumifs for Multi Select Drop Down
I have a column that is setup as multi select. I would like use sumifs but only sum the 1 item from the multi select. Do I use an OR statement? For example: column 1 is called task type, 1 row has outdoor, indoor, flooring; column 2 is called hours worked and the value is 4. The calculation would only add 4 not 4+4+4=12.…
Add x months to a date
Hi All, I have being googling for an hour and haven't figured it out. I want to add a number of months to the date in column [inspection date]. The amount i will specify in column [inspection period]. That is to say that i end up with a date 6 months on from the inspection date. I can then create an automation that will…
Autocomplete date range
Hi All, This is likely something quite straight forward that I'm over looking, but hoping someone may be able to advise - Still relativly new to smartsheets. I have a smartsheet, in which each row reports based on week numbers, such as the below formula. =COUNTIFS({Enquiry Tracker (V1) Range 4}, <=DATE(2020, 9, 6),…
Decimal Point formula help
Hi All, Please can you help me with this simple formula the formula in 'Net Revenue' =[Sell (LOCAL CURRENCY)]@row - [Cost (LOCAL CURRENCY)]@row 'Rev %' =[Net Revenue (LOCAL CURRENCY)]@row / [Cost (LOCAL CURRENCY)]@row but it does not like the decimal places which we need for accurate numbers Thanks for help in advance

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Popular Tags

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions

Using JOIN/COLLECT and an OR?
I'm trying to write a formula using the JOIN and COLLECT options. However, I've hit a sticky point when I want to include an OR criterion on the COLLECT portion. My current formula (which works perfectly) is: =JOIN(COLLECT({Assets Range 1}, {Assets Range 2}, [Tool]@row, {Assets Range 3}, "Live"), ", ") However, I want to…
INDEX/COLLECT with both MATCH and CONTAINS criteria
I am looking for help with a cross-sheet INDEX/COLLECT formula. I need to MATCH in one criteria and CONTAINS in another criteria. I can get them working separately but I don't know how to combine into a working formula. Source Sheet: Destination Sheet: In the Attending School column in Destination Sheet, I'd like to…
Parent Color Symbol, Based on "Average" of Child Symbols
Okay, here I am again! On the Project I am working on, the Stakeholder wants parent rows for no reason other than to be able to expand and collapse the data. I nicely suggested that since the data would only be for 1 year (broken down in quarters and about 5 users listed each quarter), that since the data was not excessive…

Formulas and Functions

Discussion List

Help Article Resources

Categories

Popular Tags