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.

Can an automation reference a column from a cell value?
I'm trying to track which users are granted access to various systems. What I would like to do is to have one grid which lists all the System IDs (and various properties as columns) with one system per row, and a unique System ID for each system. Then I would like a grid that lists each user as a row, and the column…
Count(Distinct Formula Help
I am currently using =COUNT(DISTINCT(IF(AND({Updated Accrual File 1082024 Range 9} = "Project Canceled", {Updated Accrual File 1082024 Range 1} <> ""), {Updated Accrual File 1082024 Range 1}, ""))) I am trying to count CompanyID's that are distinct and remove any duplicates (due to groups of Client ID's that belong to one…
How to count criteria in a multi-select column
I have a sheet that tracks the priority level of funding requests and I am trying to formulate a metric that 1) counts the number of requests under a particular priority level and 2) only counts the request if it is under a particular Division Group. The issue I am running into is that the priority levels are multi-select,…
Formula for identifying schedule conflicts with multiple assigned people
I have been trying with no success to create a formula to identify schedule conflicts if multiple people are assigned. The formula works fine with a single person assigned. For the test case, I am using a separate table (on the left) to identify when people will not be available. The right side is representative of typical…
Index Match on same sheet only matches to the first key found at top of row
The Index Match on same sheet only matches to the first key found at top of row where I am expecting it to look at all rows in the sheet. Here's the formula in Col3. =INDEX([Capacity 1]@row, MATCH([Collapsing 1]@row, Key:Key, 0)) And the example is below: You can see that the ABC12345 under Collapsing 1 col matches to the…
Count Number of Days Past Due
Ultimate goal: Create a formula that will count the number of days an item is past due, while giving credit (by counting it as zero) for items completed early or on time all while ignoring where a due date may have an "N/A" or other issue in the field. This formula will then be used to create an average day past due…
Automation randomly causing an Infinite Loop error
I am in need of some help with this automation. It stops running intermittently because of an infinite loop. Any help is appreciated.
Flagging double booked dates and rooms
Hello I am trying to get a formula that will crosscheck the camp / rooms and flag if they double book rooms in the same camp on the same days? If that can be done can I set it to reply back to the customer who books via the form? Thanks for any assistance, I am very new to smartsheets
Matching 2 Criteria in Source sheet to reference 2 other Criteria in Target Sheet
Hi, I'm trying to use 2 variable data points in a Target sheet to pull 1 data point from the Source sheet that shares the same Criteria and just got stuck, don't regularly use INDEX formula and looking for help - hope the below is detailed enough: Source Sheet: Source Sheet has a a "Supplier" column and "HV Breaker…
Formula to populate a cell based on another cell's formula criteria
Hi Again, I have a formula in row 1 of column Project Status that identifies the last value in the column that has any status except Not Started: =INDEX(COLLECT([Project Status]:[Project Status], [Project Status]:[Project Status], <>"Not Started"), COUNTIF([Project Status]:[Project Status], <>"Not Started")) In the example…

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

I have the Year and Month on a sheet. How can I have the summary look at both to count?
I would like to create a summary if the month is Sept AND the year is 2024 to count. I have found that I can use the and but keep getting an error message This is the Formula I have in the Summary that is coming up #Unparseable =COUNTIFS([Next Renewal Year]:[Next Renewal Year], "2024") AND(([Next Renewal Month]], "Sept")
Formula Help
Hi All - I am hoping someone might be able to help me. I am terrible at formulas and I cant seem to figure out how to pull what I need on my own so I copied a formula from another sheet we have at our company. The issue is that it returns it multiple times. So the expected result is "four", the formula returns "four"…
Are You Able to Join Two Columns into a Data Shuttle Dynamic Dropdown?
Hi everyone, I'm hoping someone here can help me out with a challenge I'm facing in Smartsheet's Data Shuttle. I've been trying to use expressions to combine two columns into a single dynamic dropdown column in a target sheet, without changing the source sheet. For example, I want to combine Num and Name into a format like…

Formulas and Functions

Discussion List

Help Article Resources

Categories

Popular Tags