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.

Joining Formatted Cells
Hello All, First time caller...Is it possible to join cells where text in those joined cells have different colors and have the joined cell retain all the various text colors? I've tried =Cell1 + Cell2... and =Join(Range) and both return combined text in default black text. Thanks in advance for your feedback. DT
Weekly Tracking for a Dashboard
I have a grid where I am trying to lookup from a grid that people can log issues on. The first lookup is the date range, we want to count all issues within that date range. Then we only want to count by the "Field". On the log it is the column titled Field it is a dropdown of those fields listed on the Weekly trend grid as…
Countif after certain date
Hi! I'm trying to create a formula that will count the number of rows that have a "Delivery Date" after 1/1/2019. Any row with a date prior to that should not be counted. I also do not want blank rows counted. In the screen capture attached, you'll see all but one populated row should be counted. Any suggestions on a…
Formula with COUNTIF and Checkbox
I have this working formula: ="Unit 2G is " + ROUND((COUNTIF([Unit Name]:[Unit Name], "2G") / 14) * 100) + "% Completed" 2G could also be 3G, 4H, and 2H. (Dropdown selection list) I have a checkbox column named: "Task Completed?" How would I use the working formula to not do the calculation until the checkbox is "Checked"…
effort and allocation-based timelines?
We have a small team, with each member having very a specific area of expertise. As such, our timelines are defined by prioritization of tasks, dependencies, effort, and resource allocation. I want to enter my tasks, set their dependencies, effort in hours, priorities, plug in the resources and set their allocation, and…
Formula for calculating installs for a given quarter. Q1, Q2, etc..
i have this formula for calculating installs YTD for a given property. =SUMIFS({Mira Santi Install QTY}, {Mira Santi Install Date}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY())) Not sure how to take this formula to use it to calculate installs for a given quarter. Do i just put the date range for the quarter? i.e Jan 1,…
INDEX(MATCH) returns #INVALID COLUMN VALUE
I have two sheets with a common "Name" column between them. I would like for a checkbox in the second sheet to reflect in the first sheet, factoring in that not all of the names will be used in the second sheet. I would normally use INDEX(MATCH) for this purpose. MATCH() is able to return the row number for the Name where…
Calculating Count of Projects by PM by Month
Hi, Trying to create formula to calculate How many projects start in a certain month by each Project Manager - My excel formulas are weak Master sheet has columns: Project Manager is Contact List (does this have to be a Text/Number Column?) Project Start Date - is Date type Column =COUNTIFS({Keller Project Summary - MASTER…
Formula for finding the first and last days of a year
I need to be able to calculate the first and last days of the year based upon the current date. Can someone help me out?
IF formula issue
The formula I've created isn't working correctly and I can't figure out what I'm doing wrong. =IF([New NCN Score Total]120 = 15, IF([New NCN Score Total]120 <= 14, IF([New NCN Score Total]120 <= 9, IF([New NCN Score Total]120 <= 6, "Very Good"), "Good"), "Poor"), "Very Poor") Scores 15 = Very Good 10 to 14 = Good 7 to 9 =…

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

Help with formula
I am currently using this formula and it works just fine. =IF(AND([90 Day Window 3]@row = "Ready", [60 Day Window 2]@row = "Ready", [30 Day Window 1]@row = "Ready"), "Ready", "Not Ready") But I am trying to add another part to it that has 2 things to referance to make true but does not work and Im sure its becuase or the…
Determining the # of days formula
Im trying to calculate the number of days for a project based on the budget dollars $14,256.90 with an average of $30.00 per hour per employee for 4 employees (resources) and 8 hours per day
Assignee reporting for all tasks during specific date ranges
I have been trying to create a report that shows all the tasks my team have on each week. I found some guidance in a community post about setting filters based on the start and end date. However I have found this doesn’t provide quite what I need so I was wondering if someone might be able to help me. Basically, I want to…

Formulas and Functions

Discussion List

Help Article Resources

Categories

Popular Tags