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.

How to return last match at specified indent level?
I have a master sheet of project tasks sorted by geographies. I'm looking for a formula that will return the Project Name (i.e. Project 1, Project 2, etc.) for each of the tasks under the Project. Since each "Project" is an indent level of 2, I've been trying to create a formula that would return the Project name by…
Automation
Hello, I'm trying to create an automation that states that, when a document has been approved/declined by the owner then the QA manager will receive an alert. But I also I wanted to continue that automation that states that anyone's email that is stated on the form should also get an alert. Once the form is filled then the…
How to calculate percentage done automatically
Hello, in the picture below, I am trying to calculate the percentage done based on how many columns have the check mark on them. Is that possible?
How to prevent IF function from removing cell data when false
My team is currently tracking customer services and monthly rate before and after talking with an agent, using a form that I created. I am trying to set up a formula that if we select that there are no changes made to the account it will copy over the data from the incoming fields to the outcoming. I am using this IF…
Counting items within a cell
Hi team, I am wanting to keep track of why people are exiting the company on a monthly basis. I have the following formula which works perfectly if the exiting employee only selects ONE reason why they are leaving - =COUNTIFS({HR-559 Exit Interview Range 1}, OR(@cell = "Better job"), {HR-559 Exit Interview Range 2},…
#Invalid Ref
I'm trying to reference another sheet and get a count of items that meet multiple criteria. My formula syntax is =COUNTIFS({173000 High School UUU Model Review Items Range 1}, "3-Critical", {Phase}, "CD") Do I need the function HAS? When I've tried inserting HAS, I get UNPARSEABLE. With the syntax I have now, I get…
How to have my status reflect a percentage complete
Hello, Column B has my dropdown option of Statuses (Complete, In Progress, Waiting, Not Started, & Escalated) Complete = 1, In Progress= .75, Escalated .5, Waiting= .25, and Not Started = 0 Column C is a percentage. I would like to have Column C automatically update when column B has a dropdown selected. Current formula…
Sheet Summary
Hi Team! I have an invoice tracker, and I'd like to use the sheet summary to indicate 2 different fields: "Total Paid" and "Total Due" My columns are "Amount" and "Paid Date" " For "Total Amount" I am using =SUM(Amount:Amount). "Total Paid" - If a date is populated in the "Paid Date" column, calculate the the total sum…
Show 0 if blank cell
Hi there, I have a formula that helps us determine the number of hours a project will take based on the modules created. [# modules *30]+120 The formula obviously always states 120 even if there are no modules to work on, how can I fix this to show 0 or blank cell. I've tried IFERROR, ISBLANK and IF statements and can't…
Proceeding Zeros to an already existing column.
Hi, I'd like to add Proceeding zeros to an already existing column Currently there are over 2000 rows in the, with a varying number of digits from 1-4. I'd like them all to be a 4 digit number. So if the column has an entered number of 1, I'd like the proceeding zeros to make it a 0001 or 501, but 0501. Is this currently…

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