Formulas and Functions

Archives for September 2020

Help with a complicated formula
I need all of this written as one formula. I've been trying, but can't make it work. Can you help me? If today’s date > 9/30/20 and % Complete is < 75% At Risk (Red); if today's date is > 10/30/20 and % Complete < 85% At Risk (Red); if today's date is > 11/30/20 and % Complete < 95% At Risk (Red) ("Red" = Heatmap Rating)
Automated Workflow Notifications
Hi everyone, I've created a sheet with multiple points to send a notification based on the date. Several columns are irrelevant to our work and are used simply to send a notification (i.e, if a checkbox is marked because month date and year is equal to today) then it sends people a notification. These reminders are sent at…
Needing some help on a =countifs formula
Hello, We are trying to count how many service calls our techs preform weekly, monthly and yearly. I have tried different formulas can cant seem to get anything to work. I thought if I use the this formula I can change the email address to the techs initial's then count the initial's in a different cell. We want to use…
IF AND Statement for flag using RGY status
I am trying to write an IF AND Statement for flag using RGY status. If Scope, Schedule, Budget or Customer Satisfaction is ac Yellow or Red symbol, the flag will mark red. I have tried =IF(OR([Scope]@row = No, Scope@row = Hold)) as a test without Schedule, Budget or Customer Satisfaction to test am getting an error of…
How do I get a sumif working with two date columns as comparison conditions?
Trying to get this formula to work but kind of new to Smartsheet. =sumifs(metres:metres, completed date:completed date, @cell<required date@cell) I don't know I should be using a flag column, there's already a fair bit of data knocking around and I would rather just get this to work for the sheet summary field.…
concatenate 2 different values
This discussion was created from comments split from: How to concatenate two functions in a single cell.
Count children row's checked boxes formula request
What would be the formula that would count the number of checked boxes in a single column, within children rows under a parent row?
COLLECT and MAX
I have been trying to find the right combination of formulas to do this in place. I am trying to report the name of a contact in the same row as a max value. I have attached an example photo to help describe my situation In the scenario, I am trying to look up the maximum number in the Parts Column and return the managers…
Combining a Nested IF Formula and Searching for the First 4 Numbers in a String
Hello Smartsheet Community, I am trying to build a Nested IF formula to assign a site name depending on the first 4 numbers in the screening participant ID. So for example, I would want the formula to identify "S401" from the string "S401001" in the Screening ID Column and assign the name of "01/New York" in the Site…
Help with DISTINCT Function
I need to create a list of unique names pulled from a column on a different sheet. I used this example to get things generally working. However, I'm not ending up with a truly unique set of names. The formula in question is: =JOIN(DISTINCT(COLLECT({Fruit}, {Fruit}, @cell <> "")), ",") + "," Here's an example of how my…

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions

Sheet Summary Formula
I am looking to count how many letters we have issued within each month, this is by going by the modified date, these are the formulas i have tried as i want to start this from August: =COUNTIFS(Modified:Modified, MONTH(@cell ) = 8, Modified:Modified, YEAR(@cell ) = 2025) However, it comes up with an error. Any ideas…
How to Create Time Duration
I have a report where I need a formula to figure out time duration. For example, employee starts at 8:00 am and ends at 9:00 am, I need the next column to tell me 1.00 to indicate he worked 1 hour. I will be using military time throughout this sheet.
#BLOCKED / Auto-Save Issue in Project Billing Forecast Report
We are still facing a critical #BLOCKED / Invalid Value issue in our Project Billing Forecast Report. 📌 Current behavior: In the underlying Sheets, formulas (e.g., Total Billed This Period, To Date Billed) initially show #BLOCKED/Invalid Value, then refresh to correct numbers. However, the Sheet does not auto-save this…

Formulas and Functions

Discussion List

Help Article Resources