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.

Is there a function to have google sheets and smartsheet to have a live active link?
My company currently has two separate department that uses two different systems. My department is using Smartsheet and the other department is using Google Sheets. Is there a way for the two systems to communicate with each other with links and formulas?
Check box behavior
I need a formula to: if document number column is a duplicate entry then the checkbox needs to be checked and if the document number is blank or a non-duplicate value then unchecked- can Smartsheet do this?
Calculate percentage of value based on the day of the week
I'm working on a project for one of my customers. I need to be able to use the historical percentage of containers arriving at port on each day of the week to project the number of containers that are going to arrive at port in the future based of the forecasted volume in that given week. Struggling with the formula to…
Error counting Children Completed tasks referring another sheet
Hello, I have a Summary sheet to have all counts of my sheets. I am trying to use this function: =COUNTIF(CHILDREN({Product Release Roadmap Intervalo 3}); "Complete") to have the count of the completed children tasks in anothet sheet named "Product Release Roadmap" but it is returning "Unsupported crossheet formula" Can…
Countifs with a range based on greater than and less than
I'm having an issue writing a formula that I thought would be pretty easy. Essentially, I have a Sheet Summary of Tasks In Progress that I want to count all rows that have "Task" checked and "Progress" is less than 100% and more than 0%. My formula for Tasks Not Started works by counting only the 0% and my formula for…
Assign Numerical Value to Text Field
Good afternoon, I'm trying to assign a numerical value to a text field. I have classes that staff can select to have us instruct. Each class will have a time assigned to it so class A might be 1 hour and Class B 2 hours. When staff select Class B they will also inform us how many students will attend and I'd like to…
COUNTIFS Function
Hello everyone, Need some help with this formula, thinking i have too much going on here? =COUNTIFS({Environmental Incident Tracking Range 1}, AND({Environmental Incident Tracking Range 3} = 1, YEAR({Environmental Incident Tracking Range 4} = 2020, {Environmental Incident Tracking Range 1}, "Leak"))) Environmental Incident…
Time Duration Calculation
I've seen a few ideas on similar topic threads but none of them worked quite right, so I'm trying again! I have a form staff fill out to record their overtime and the reason for it. Does anyone have any formulas that I can use to populate a column showing the numbers of hours:minutes of overtime based on the "Scheduled Out…
Hyperlink to row in another sheet based on a formula
I'm using =Match() to look up a value in another sheet which results in the row number of that sheet. I would like to create a hyperlink in the original sheet that will take me to that row in the 2nd sheet that is displayed based on the match formula. Example: Match formula in sheet 1 references row 5 in sheet 2. Need a…
Using Multiple Checkboxes to determine and auto-update % Complete
I have some tasks for an implementation Smartsheet that require different steps. I would like the % Complete column to auto update to 33%, 67% or 100% based on the boxes checked. Obviously if the final box is check it would be the 100% and ignore the 2 boxes that came before it. I attached a screen shot with the formula I…

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