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.

SUMIF sum of the number of cells meeting a text condition
I'd like to add up the number of boxes with the same condition. For example, in column 1 I would like to count how many times I have the word nature. When I use SUMIF to do this it doesn't works.
Formula to populate parent row with children row data
I need a formula that will populate a parent row with whichever child row is NOT blank, I would want the parent row to copy the same sentence that is in the child row into the parent row. The reason I need this is because I am using the document builder sheet to create status reports so I need the parent row to pull…
Automation triggered copy one cell to another on same sheet
Hi , I would like to be able to "check" a box in col A which will trigger a copy of the content in col B to Col C (but not change value as long as the checkbox is checked) I want to use this to flag a specific line/task as one which is due data is important for me so I want to copy it to a column "important dates" but the…
Date Formula Issues, Metric sheet
I am very new to smartsheet. What formula would I use to count how many times the year 2021 appears in the blue (parent) rows? I also would like to make a formula for a metric sheet that automatically updates as I add and delete years from my main sheet. What formula would I use for that part as well? I've tried a few…
Formula to use to match data from one sheet to another then calculate the total
Hi All!!! I'm terrible at formulas and trying to learn! I need your help PLEASE! I have a Master sheet where everyone places orders, it contains the an account number to be charged to and total for each order, in a separate sheet I have all of the account numbers. In the second sheet I need to create a formula that for…
% complete using a duration of dates
I am trying to get a percent complete using dates. For example, I have a start date and a finish date to get the duration (let's say that is 96 days) and I want to find out the percent complete using today's date (automatically updated each day) as the task progresses in time. I can't seem to figure out how to use TODAY()…
Does anything in range A appear in column B?
I am trying to set up a formula that lets me know if any of the Project Tittles in one sheet appear in any comments on a separate sheet. When the function should return "True", I get "#invalid data type" =IF(CONTAINS({Project Tittle Range 1}, [Comments]:[Comments]), "True", "False")
Formula to match data from one sheet to another, then calculate the total if a different column true
STUCK in Formula H_ll! lol I'm terrible at formulas and trying to learn! I need your help PLEASE! I have a Master sheet where everyone places orders, it contains the an account number to be charged to and total for each order as well as the status of each order, in a separate sheet I have all of the account numbers. In the…
How to make widget that counts year in date column?
I am making a dashboard, and I would like to make the specific year's number change as I add/ delete sections in my sheet. (right now each number is manually typed from a report I made). I have each item filtered by year, and then each item has its own specific date on the sheet. What formula can I use to do this on a…
Next Task on a dashboard
I am trying to create the above to appear in a dashboard. I have been able to pull in everything (Except the data bars as it seems that is not part of the conditional formatting) but I am struggling to figure out how do I pull in the next step. It should be the either the task that is in progress or some of the team wants…

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