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.
Discussion List
Looking for formula
need to populate a date with these parameters. Defect type ="CRITICAL" - populates "clean up date" with date 5 days after "audit received date" but if Defect type = "major" or "minor" - populates "clean up date" with date 30 days after "audit received date" right now i have simple formula to populate Clean up date as 30…
The Average Number of Monthly Projects
I want to be able to have a formula that pulls the average number of projects we complete monthly for our sheet summary. I have a date column for "Date Completed". Can anyone help with what the formula would look like?
Date add 35 days
I am looking to enter a date 04/02/2020 and then add 35 days. All of our projects have a deadline of 35 to close. Would love any help.
Sequential Unique identifier for two types of records in a sheet
Hello Smartsheet community. I am in need of a solution that allows me to leverage an intake form to track Objectives and Key Results. I need to be able to sequentially generate unique ID's for each Objective and for each KR and I need this to not break with sorting. The reason is, I need to do lookups on the Unique…
=SUMIF Formula
I'm looking for some guidance with this formula. I can't seem to get the numbers to add up if the condition is met. Essentially if the cell is "No" a number should be added and the number is different based on the column. Those numbers should then be added for a total. Here's what I'm working with... =SUMIF([Column]1 =…
How to copy a parent row through automation without also receiving children?
I have tested several automation options and am unable to only copy parent rows. I can set the criteria to only copy a single or multiple children without parents but, only want parent rows of data to be copied.
#INCORRECT ARGUMENT SET in Nested IF statement
Hey All, I think I have all my ducks in a row here, but I continue to get a #INCORRECT ARGUMENT SET Error. Trying to dynamically set a status indicator. Can someone assist? For more info: [Assigned To] is a Contact List [Resolved] is a checkbox =IF(AND(NOT(ISBLANK([Assigned To]@row)), Resolved@row = 0), "In Progress",…
COUNTIFS Using Mulitple Criteria
I'm trying to get a count of rows where COLUMN 1 displays “Med” or “High” and COLUMN 2 displays a date in the past. as. well as... count of rows where COLUMN 1 displays “Med” or “High” and COLUMN 2 displays a yesterday's date. Any help would be greatly appreciated!
Need to create a "shift" column from a time column
I have a column with the time (hh:mm) an interaction took place. I have another (currently blank) column for shift that I need to populate from the time. The "shift" during which an interaction took place would be: 7Am-12:59PM as Day, 1-8:59PM as afternoon and 9PM-6:59AM as night. I'm not sure where to start. How do I…
Trouble with SUMIFS - Project
Our sheets have built in deliverable risk management which we are working to roll up across projects. Right now we calculate a delay risk by looking at a risk/time allocation, and critical path - both which are columns within the same sheet. The following formula is working correctly: =SUMIFS([Risk/Time Allocation…
Help Article Resources
Trending in Formulas and Functions
Nested INDEX/MATCH formula for conditional cross-sheet reference use case
I am currently using the following INDEX/MATCH formula to return values from the sheet labeled "Tech Project List" in the reference. =INDEX({Summary from Tech Project List}, MATCH([Tech List Row ID]@row, {Project ID from Tech Project List}, 0)) I now want to expand that formula to include a condition: If #NO MATCH on Tech…
Sum cells based on multiple criteria with a timeframe requirement
I am working on trying to sum the time I spend across all my projects and want to sum different columns but only when they meet different criteria. Every formula I work through doesn't seems to work. I want to sum the Duration of Meeting IF the start date of a subject is within 5 days of the date in the green box that I…
Formula to populate field from another sheet using multiple criteria
Hi there, In one sheet ("pay scale") I have our employee pay scale and in another ("Employee Salary") I have our employees listed with their experience and education levels. Is there a Smartsheet formula that I can use to pull in their salary based on their experience and education levels? For example, if they have a BA +…