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.

Return a value if a cell date is between 2 dates
Hi, I want to automate one of my sheet summary by retrieving the Sprint# based on today's date. (When today's date is between the beginning and end of a sprint, I want the sprint# to be retrieved in the Automated Sprint field of the summary sheet). I have tried to use =INDEX([Sprint#]:[Sprint#], MATCH([Sprint start…
Using Sumifs and Month referencing another sheet
Hello! I am trying to sum the values occuring in a particular month referencing another sheet. Here's my formula: =SUMIFS({Summary Roll-Up - Machine Accepted}, {Summary Roll-Up - Machine Accepted}, >0, {Summary Roll-Up - Delivery End}, MONTH(@cell) = 1) The first part of the formula is working correctly, but when I add the…
Splitting data across multiple rows based on dates
Hi Everyone! As a Project Manager I would like to report total scheduled hours per week for an individual team member. Does anyone know how I can go about doing that in SmartSheet in a way that will update if the project plan is updated? I would prefer if this was performed in a separate sheet instead of the project…
Trying to Autofill filed with Text based on data submitted by form.
I am currently working on a sheet where pending on the multiple options they chose it field A it will Mark other fields as requested. Then once the resource is assigned we can mark as assigned. How do I do this in Smartsheet. I have tried some things but not sure what I am missing. Any help is appreciated.
Metadata sheet pulling data from 40 project plans into 1 dashboard?
I am working with sheets, already created. There's 1 project plan and several sheet i.e. a RAID log and a Decision log that go along with each project. Then there is a metadata sheet (1 per project) that pulls info from the project plan and calculates it and then it gets displayed in a dashboard. The lastest ask is a…
"Sprint#" dropdown single-selector that inserts Start Date & End Date
Seeking help on crafting a sprint calendar widget. I've set up a dropdown selector for "Sprint" - a single-select text cell with name: "Sprint 112" or "manual entry". I would love it if I could have the the selection in this cell fill the start and end date cells (or not, if "manual entry" is selected). I'm using a…
Project Template
Hello! I am utilizing the Project Template to create a Comm Flow plan. I am getting bit hung up on the predecessors and dependencies. I need the calendar to highlight 3 things. Is it possible for the sheet to highlight 3 separate dates on the calendar without being a bar? AND is there any way to make those 3 separate dates…
How do I account for a specific status in a formula calculating expiration status?
I am using the following formula to count the number of contracts that are expired =COUNTIF([Contract Expiration Date]:[Contract Expiration Date], <TODAY()) I want to exclude any contracts that have "Archived" (Value) as the "Current Status" (Column) from this count.
COUNTIF Date Next Month
So, I'm trying to create a sheet summary formula that will count the number of milestones in my project schedule that is due next month. Because I have a section in my project schedule for milestones that is linked to various tasks throughout the project schedule I'm able to use the CHILDREN() function to limit the range.…
Is there an easy way to search two cells to see if they match?
I've got two columns, ones a Contacts column with a drop down select, the other is a system column that is automatic and puts in the user email address. Both populate via a form. Is there a simple way to search both these columns and check if there's a match and display something like MATCH or NO MATCH? Thanks.

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

Sum of only last child in cell range
Hi there I would like to count the values of only the last children in the column. Some cells have one indent, some more, so I would like to know the formula, to count only the last indent children. Below is an example what I would like to achieve. The result of the formula should be 350. Additionaly I would like to…
Duplicates in formula-generated column
I use formula =IF(COUNTIF(Column:Column, Column@row) > 1, 1) to find duplicate values within a column. However, this formula does not work if the value of the source column is generated by a formula. For Instance, ColumnC = ColumnA + ColumnB. The above referenced formula does not work when trying to find duplicates in…
How to update certain cells in column based on one cell's input and other column's same ID #?
I have a very simple test script testing tracker located here: I want to be able to do one of the following: 1. Update the Status column for all testers with the same test ID to "Duplicate" once one person tests and puts "FAIL" in the PASS/FAIL column. 2. Do not copy defects to defect tracker sheet if one of the rows with…

Formulas and Functions

Discussion List

Help Article Resources

Categories

Popular Tags