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.

HELP!!! Max Collect for Dates
I utilize Smarthsheet to manage a hospital schedule. I need to find out the last time a staff member worked. I have been playing around with Max Collect and constantly failing… #UNPARSEABLE Things to note: The Primary column shows the name of the staffer multiple times with multiple different dates under the Date Worked…
Populating parent row status based on different combinations of status from child rows
I am using the formula below to populate my parent status but I also want the parent status to say "In Progress" if the child rows are any combination of "Complete" AND "Not Started". Right now when the child rows are a combination of "Complete" and "Not Started" the status will read "Not Started". How do I get that…
Need to highlight duplicates in two columns?
I need to highlight any duplicates in two separate columns. Such as below screen shot: Need all # highlighted that are in Unit # column and the Asset on Rent Data. Its easy in excel, but can't figure it out on Smart Sheets.
Auto-populating Avg. of reported data and dispersing among different "Teams"
I created a form to input data that is collected on a sheet. My sheet correctly displays the correct information. I have Columns listed as Room 100-Room 110 as well as a Total column. Each room can have a score of 1-6. My total Column correctly displays the correct number when you add all the scores together for Room…
SUMIFS with criteria in 2 columns of different sheet
I am trying to create a Metrics Sheet referencing three columns of another sheet. I want to sum the Officials column of the {Officials Reg Numbers} sheet if the Sport column = Sport@row and the Year column = Year@row of the metrics sheet. =SUMIFS({Officials Reg Numbers - Officials}, {Officials Reg Numbers - Sport},…
Cross Sheet Index, Match, and IF
Hi, Thank you for coming to my help, I really hop this question makes sense. Basically, what I want to do is take the closest date of incomplete tasks and put it into another sheet where I can run automation asking someone to update a report. The reason I want to do it this way is because on the report you could be…
Showing multiple dates from the same sheet row in a calendar
I have 6 columns in my Smartsheet that are date columns, two of which are start and end dates. I have got the start and end dates to appear in a calendar report, but the other ones (individual dates, not date ranges) do not show up. I cannot have them as children rows. I have tried applying conditional formatting to the…
Column formulas
Can I exclude one cell from a column formula?
Task Health Indicator Formula (IF/AND/OR)
Got burnt out looking for my exact scenario in the forums, so looking for direct help. Trying to create a formula to automate my Health column KPIs based on task status and assigned dates. Status options: Not started, In Progress, Blocked, Complete Desired Status Colors Blue: Status = Complete Red: Status <> Complete AND…
How to record automatically closed hour
Dear all, I would like to record the exact hour when a ticket is closed. is there a way to do that? thank you in advance for your support

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

No Match...but the formula works
Can someone help me with this? I have a formula which works but it's finding NO MATCH for two lines but I don't know why as the formula has no problem finding a different line on the same sheet… This is the formula: =IFERROR(IFERROR(IFERROR(INDEX({IQA Log Record Closed?}, MATCH([Primary Column - IQA Ref]@row, {IQA Log IQA…
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…

Formulas and Functions

Discussion List

Help Article Resources

Categories

Popular Tags