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.

Index/match and Isblank?
Hi, I've got an Index/Match formula that looks for the latest date on a row and references a particularly column on that row. Example: =INDEX((pH:pH), MATCH(MAX(Created:Created), Created:Created, 0)) I'm after a way to make it return a set text "Awaiting Results" if the cell is blank. I tried IFERROR() but that didn't see…
Sumifs to roll up project hours?
I'm trying to sum up the total hours for a project based on a given date. However, the hours and project names are spread across 5 different columns for hours, and 5 different columns for project names. My Sumif comes up as Incorrect Argument. Do I need a combination of Countif and Sumif? Example, I need to calculate the…
Is there a Hierarchy level value I can use
I want to create reports for different level of hierarchy, i.e. a report only extracting top level Parents, then a report extracting only Level 1 Children, then another report extracting Children of children etc. One way I though of is having a column with a value for the Hierarchy level, i.e. Parent =1, Direct Child = 2,…
A way for Smartsheet to link to my company's AD?
Is there a way for Smartsheet to link to my company's AD?
New to Smartsheet formulas
I am trying to calculate "years of experience" based on the certification start date 1/1/2015 through 02/21/21 certification expiration date. The start date is static and the expiration will change every 2 years. I clearly am not using the correct words in the help section to get my answer. Please help. Thank you.
How to create absolute cell reference in sheet summary
Dear Smartsheet experts, I created a Smartsheet that captures weekly figures per location (via a form). I then created a portal to show both a report of all figures, as well as just the newest figure per location. To make the latter widget work I did the following: 1) Created a sheet summary that figures out the latest…
Automated WorkFlows
I am trying to set up Automated workflows for my Boss, so it will send an email (For example) 5 days after a date in a column. More or less Follow ups, and also ones to remind me Due dates are coming up. Here's what i tried so far. When Rows are added or changed.... when Column X changes to "any Value", where Column X is…
Using IF AND OR Formulas
I'm trying to populate a column "Risk Impact", based on values from the column : Severity and Likelihood. The values are as follows: Value should be Medium When : Very Unlikely vs Minor Unlikely vs Significant Possible vs Significant Major vs Likely Major vs Very Likely Severe vs Very Likely Value should be Very Low When :…
MAX Function Error (#INVALID COLUMN VALUE)
Hello, I am working on creating a project plan. Instead of having the singular Status Update field that comes with the project plan template, I wanted to keep a log of status updates, then reflect the most recent info from that log in my project plan. I created a new sheet/grid with 3 columns. Weekly Comment, (automatic)…
=COUNTIFS and AVG
Hi, How can I make an average of "Jack" based on the "Amount Column" column? I tried doing the below, but i don't think this is the average of the "Amount Column" Column. =COUNTIFS(Name:Name, "Jack", [Amount Column]:[Amount Column], AVG(0, 0)) Pls help me out. Thank you!

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

Is there a way to stop automations when the person being notified is the person that trig
Is there a way to stop automations when the person being notified is the person that triggered said automation?
Check a Box if Between Dates on Another Sheet
Hey all, I am trying to build a formula to check off a box on my sheet if at least one date is found between two dates on a separate sheet. I've tried a million variations but here's what I have so far, thinking I was overcomplicating things =COUNTIFS({Approve Date}, >=[Week Starts]@row, {Approve Date}, <=[Week Ends]@row,…
Calculating the total duration from a date & military time start and end
I have been looking at a lot of information on this site and this is the closest one I have found, however it does not calculate the minutes correctly. For example instead of 8hrs and 45 min it gives me 9hrs and -15min. Formula: =(([On-Site Date]@row - [Notification Date]@row) * 24) + VALUE(LEFT([Time of Crew arrival]@row,…

Formulas and Functions

Discussion List

Help Article Resources

Categories

Popular Tags