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.

IF/AND Formula
I am trying to create a formula for when two approval columns have 'Approved' in their dropdowns to change two other dropdown columns. When "APPROVAL Status - BOB" = "BOB - APPROVED" AND When "APPROVAL Status - JOEL" = "JOEL - APPROVED" THEN "Approved?" column will change to "Approved" AND "Issue Status" column will change…
Formula help - adding criteria to LARGE?
Seeking some advice on how to make the LARGE function play nice with criteria and possibly, I'm thinking, the COLLECT function. Here's our set up: "Master project sheet" with: 1) "Project name" column 2) "2019 total hours" column which is a cross-sheet reference SUMIFS formula 3) "Project priority score" column which is a…
Sum if not blank
I am trying to sum across multiple columns but have it show blank if the columns do not have values. Each of the columns has a formula in them to calculate a number. However, I want the sum column to show blank instead of 0 if all of the other columns are blank. My formula right now is =SUMIF([Question 1…
Auto-hiding rows
Not sure if this is possible, but I would a row to become hidden based upon the status of a drop down. If cell is blank, nothing needs to happen. If cell is "delete" or "archive", I would like the row to become hidden. Any help would be awesome.
Formula HELP - Trying to total cells that belong to specific market
I have tried several countif and sumif formulas but am at a loss. I have an expense report that has a market column (think departments) and a Project Costs column (total paid for the project). There are multiple markets to select from. I am trying to get a running total of project costs for each market. In simple form, my…
RAG Roll-up formula
Could anyone help me with a formula I'm trying to perfect. I am trying to create a formula that automatically captures RAG status for Children Rows and how this is communicated to the Parent rows. The plan is essentially structured like this (images are attached): * Project Plan Summary (Senior parent)* Main Tasks…
Need Help! RYG to update if Status = Completed OR % Complete = 100%
Not sure what i'm doing wrong here. I want the RYG column to show green if the status is "completed" OR if the % complete is 1. Please assist. =IF(OR(Status@row = "Completed", [% Complete]@row = 1), "Green", IF([End Date]@row - [Baseline Estimated Finish]@row > 2, "Red", IF([End Date]@row - [Baseline Estimated Finish]@row…
#INVALID OPERATION when dividing
I'm trying to do a count of how many staff have uploaded a particular document. I have who has uploaded the document and how many staff in total, and I want to display that as a percentage on the sheet. Whenever I try to divide # of Staff Uploaded by Total Staff I get #INVALID OPERATION. I've tried it both in formula and…
SUMIFS with Cross Sheet Date Ranges?
I'm trying to calculate {Expected Premium} sales by {Producer Code} on an enterprise-wide sales tracker that compares monthly sales metrics per [Producer Code]@row against total renewal opportunity. I've scoured the community for answers but I haven't found anything that answers my question as it regards to cross sheet…
Alternative to FLOOR and CEILING functions
Hi! I'm looking for an alternative for the FLOOR and CEILING functions that I use in Excel. For context, I purchase paper, and buy it in skids and fill the remaining balance in smaller packages. In Excel, I use a formula that references a lookup table to tell me how many sheets of paper come in a skid and how many come in…

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