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 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…
Reusing the same automation on the same triggered cell only works once
I have a simple automation - If a Status cell changes to red or green then trigger. It works fine unless I try to trigger it again by forcing a change on the cell. I thought it was because cells are locking but I unlocked them and the problem was still there. "Status" is set by checking the values of a group of other…
Using #INDEX & #MATCH cross sheet reference formula help
I am attempting to write a formula that says: IF {in sheet# 1} COI Type = "Job" OR IF Agreement Type = "Project" OR IF a row matching the code in sheet #1 matches a code in sheet #2 AND Waiver Type = "Approved-Job Specific" THEN check Approval Required box. The first part of my formula works: =IF(OR([COI Type]@row = "JOB",…
Use Status change to create a child record
Hello community, We are working on a new process flow for developers. Each row would be a project and as they move the project through different stage(Column) it would create different subtasks(Child records) to each stage. I don't want to allow them to change the stage until they have completed the subtasks for the stage…
COLLECT date values from another sheet
There are 2 sheets with the same set of columns: Code / Crop / Start / End / Cost Start and End Columns are dates. 1) I need to collect date from column Start which satisfies the conditions Code = 1122, Crop = Apple. The formula is the following: =COLLECT({Sheet1 Range 1}, {Sheet1 Range 2}, @cell = Code@row, {Sheet1 Range…

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

Get a Countif on values in a multi-select column of cells
I am trying to write summary formulas to determine how many values appear of each in the drop-down of a column. I tried adding a wildcard, I have tried HAS, and tried CONTAIN, but I'm still not getting the right values. Currently I am getting 0 as a return value on this formula: =COUNTIF([ROI Type]:[ROI Type], HAS([ROI…
summaries in report
Hi i'm trying to build a report with summaries options. i have two column that i want a Min and Max summary. the column for the Min i a texte column and works well. but the other column has formulas and the Max doesn't works. there is a way to fix that? (instead of creating another text column in the source sheets?) here…
Converting email addresses with hyphenated First and/or Last Names
I am trying to convert email addresses into a Name field with the employee's First and Last Name, capitalizing the first character of each part of the name and keeping the hyphen in any part of the name that has one. I started with hyphenated first names so taking shmoe_joe-bob@company.com and converting it into Joe-Bob…

Formulas and Functions

Discussion List

Help Article Resources

Categories

Popular Tags