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.

COUNTIFS with Jira links and Status
Hello, I am looking to count the number of open tasks from a smartsheet workflow from JIRA. I have JIRA links marked 'ITV'= the reference from JIRA (for example ITV-123) and others using TPM-and the JIRA reference. In addition, I want to count all statuses marked as Open, In progress, etc,.. I am trying this formula but…
Formula to Calculate items expiring in the next 10 days which are not closed yet
I am working on a formula on incident reporting to calculate items expiring in the next 10 days, where the status is not closed yet. Can you advise if the following formula is correct? =COUNTIFS({Expiry Date}, <=TODAY(10), {Status}, NOT(@cell = "Closed")) I look at my sheet and there are only 2 items which are not closed…
Linking sheets
Hi, I am linking a master sheet to a smaller sheet and I want a formula that will look at the master sheet and add new rows on the smaller sheet if there are some. It will update the linked fields. I am jsut trying to avoid manual work. thank you
sum IF formula
Hello Me again... sorry.. I need to summarize total number of employees across the country by location. I have column with location names, and a column with total number of employees... For example I need to know for ALL locations how many employees are Remote I tried =sumif([Location (for summary)]1:[Location (for…
COUNTIFS and NOT Function
I'm looking to count the number of instances an epic is listed, but to exclude Closed and Done ones. I am trying this formula which gives an error =COUNTIFS(JIRA:JIRA, CONTAINS("TPM", @cell), [Issue Type]:[Issue Type], "Epic", NOT(Status:Status, "Closed", "Done")) Any suggestions?
COUNTIFS within a Date Range
Hello, I have a column of dates that are formatted as dates. I am trying to use COUNTIFS to tally the number of instances in which a date from the column is less than 1 month from today, between 1 and 3 months from today, between 3 and 6 months away, and between 6 and 12 or more months away. For less than one month away, I…
Formulas Not Pulling
We currently utilize a very large global onboarding smartsheet. When a new hire accepts a position, the onboarding team uses a form to manually add them to the tracker so we can assign out the onboarding tasks and track progress. As part of a business initiative, we also have hidden column to track the amount of time spent…
Adding a date cell a cell is changed
Is there a way to add a date to a row, when a status field is changed? I tried the "Modified (Date)" but that will change whenever any cell in the row is updated. I only want the date to update when the one specific cell changes. Thank you.
Incorrect network days
Hi All, I'm trying to replicate the same formula in smartsheet, the issue is network days are calculated incorrectly, is my formula wrong? I have attached the image: from Jun 3 - Jun 19 there are 13 network days, while splitting for the week beginning Jun 15, it only shows 3 workdays instead of 5. any advise/suggestions?
Count Projects by Year within a range
Hi All! I've been scratching my head with a way to count all projects where the Start Sate or End Date have a year that matches a bucket. Fore example, if a project's start date is 2020 or end date is 2020, then I need to count it in the 2020 sheet summary. A project where the start is 2019 and end is 2020 would also be…

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