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.

Count number of tasks by day to know how many workers are needed
Hi Everyone, Brand new to smartsheet and I have very little understanding of it's capabilities. I've built an extensive gantt chart with tasks going out over a year. When I manually count the tasks, I can see that I need anywhere between 2 and 5 workers on a given day based on the number of active tasks. As I continue to…
Join Collect Function
Hello community, I am trying to collect all dates into one field when a presenters name is present. Sheet one has my presentations scheduled for 2024-2025 - (need dates pulled into sheet three) Sheet two has me presentations schedule for 2023-2024 - (need dates pulled into sheet three) Sheet three has all presenters I have…
How to match clinical trials to subjects based on eligibility criteria columns?
Hello, I have 2 sheets and I want to pull information from sheet to another sheet if certain criteria are met. The first sheet, "Slot List", is a list of clinical trials and the columns contain the study name and certain eligibility criteria patients need to meet to join the study. On the second sheet, "Trial Match", is a…
Formula not returning the correct values
I'm having a bit of a weird issue. I have a helper column that is supposed to return values that are pulled for a metrics sheet. Overall the formula is working, but it's like its not moving past the "Materials Delivered to Vendor" part. =IF([GM Status]@row = "PO Created", "Written", IF([GM Status]@row = "Delivered to…
Subtract 1 month from the date expire for a task
I have a column that calculates a due date that uses this simple formula: =DATE(YEAR([Date Expired]@row), MONTH([Date Expired]@row) - 1, DAY([Date Expired]@row)) It works great but for some reason returns #INVALID VALUE when the date expired is any day in January. any other month seems to be fine I'm guessing because the…
I need help rolling up multiple weeks of data into one response based on specified criteria
I currently have a sheet that collects weekly attendance info from multiple stakeholders (for the purpose of this question we can just look at the Coach Info columns). There are 3 responses that can be put into this column: 'Yes', 'Some, and 'None'. My second sheet is a monthly tracker in which I am trying to read four…
Getting #DIVIDE BY ZERO error message for AVG + COLLECT formula (trying to exclude zeros/blanks)
I added a Curriculum Average row (blue row in the screenshot) to help roll the data up before I run an AVG(COLLECT formula from my master data sheet. I've covered up teammate information for privacy. Currently, I am referencing the respective Region & COURSE2000 Curriculum Average in the formula below. There are COURSE2000…
Formula issue
Hi Brian Trust My formula wont work LOGIC If the Helper Date Column is blank then the priority cell is blank If the Helper Date Column is less than 10 days, its Low Priority If the Helper Date Column is greater than 10 days, its High Priority? FORMULA =IF(ISBLANK([DD 10 Day - Helper Column]@row), "",…
#NO MATCH result with INDEX/MATCH formula referencing Auto-Number/System
I use the INDEX/MATCH formula across many of my sheets for many years. This is the first year I've encountered this issue: my formula is indexing information referencing an auto-number system column type. We generate 3-digit "reference IDs" in this auto-number column to make it easy to index information for that respective…
VLookup from multi select "Search Value"
=VLOOKUP(Preceptor@row, {VLookUp for Preceptors MCE Name}, 5, false) Trying to pull one answer from a search value that could have multiple options. Preceptor@row is a multi select drop down, and I only need to pull one value from any of the possible options in the preceptor@row (because it will be the same no matter who…

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Trending in Formulas and Functions

OFFSET calculation
Hello, I am wondering if I could have some help on a calculation matter. As I have tried to find quickly some answers on the forum, it seems that you can not use the OFFSET function from Excel in Smartsheet. Right now I was using it in column H to calculate the stock of the compounds in the inventory. The goal is to add…
Sumifs
Hello, I was wondering if anyone could help me with a sum ifs function. A screenshot of the sheet is included. Basically I want a formula that says if the course name match and the date match, total the costs. So in the example if the course date is 9/16/2024 and the course name is 300, add the three rows of cost. I can…
How to create a formula column that calculates the difference between rows?
I have a sheet1 with columns labeled "Metric Name," "Plan-Actual," and monthly columns where we record the plan and actual numbers consecutively for the same metric name. Metric Name Plan - Actual Last FY - Jun Last FY - Jul Last FY - Jaug Test-01 Plan 90 89 21% Test-01 Actual 90 79 78 Test-02 Plan 90 100 120 Test-02…

Formulas and Functions

Help Article Resources

Categories

Popular Tags

Formulas and Functions

Discussion List

Help Article Resources

Categories

Popular Tags