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.

Can I count cells in a range that are not blank?
Hello! I'm trying to count cells in a continuous range that have any value in them. I have tried: =COUNTIFS([Step 1]@row:[Step 4]@row, NOT(ISBLANK([Step 1]@row:[Step 4]@row))) which resulted in an incorrect argument. =SUM(COUNTIF([Step 1]@row, NOT(ISBLANK([Step 1]@row))), COUNTIF([Step 2]@row, NOT(ISBLANK([Step 2]@row))))…
Count of request Status for each System using COUNTIFS statement with OR criteria
Hi, I have a sheet with a list of requests for various Systems and am trying to create metrics for how many "Active" requests exist for each System. I'm able to get these to work: Count of rows by "Active" Statuses: =COUNTIF({Sheet Range 2}, OR(@cell = "0 - Submitted", @cell = "2 - Approved")) Count of rows by System:…
Update Requests
Is anyone else having the issue of disappearing update requests? I send update requests and it shows the little envelope icon but then I go back into the sheet and they are not there anymore? Is there a reason for this? I also find when I delete one update request on a row it deletes another one as well? Does someone know…
INDEX multiple CHILDREN values in PARENTrow
Hi, I would like to index multiple children in a parent row. I'm using this formula if there is 3 children : ="•" + INDEX(CHILDREN([Primary Column]@row), 1) + $[Column3]$1 + INDEX(CHILDREN([Primary Column]@row), 2) + $[Column3]$1 + INDEX(CHILDREN([Primary Column]@row), 3) I'm using this formula if there is 2 children :…
Multiple Contains
I am trying to create a formula that counts the amount of instances that meet multiple criteria. I want to count instances where the Created Date is older than two days ago, the "Service Requested" is Check-In (This is a multi check box), the Branch is Innisfil, ON and the Ticket Status is either Not Started OR Assigned…
Extracting a list of unique value from reference sheet based on some conditions
Hello there, I have been trying to extract unique values from a different sheet. Suppose there is a sheet with Project Name, Project Owner and Project Type. I want to extract a list of Unique Project Owners from the other sheet who manages same Project Type. Here is an example data: I am expecting these two results after…
How to create Conditional Formatting on a TEXT cell if the status is not current
Hello all - I have a status update sheet in which I wanted to create a conditional format to HIGHLIGHT a cell if the Status Update (text) is BLANK and/or 7 days ago. The cell is a TEXT cell that includes the date + status details; I.e. "10/16 We are now live." What I wanted to do is highlight the cell if the status update…
Help w/selecting multiple columns @row that are Blank
Hi team - I have a large span of columns that I need to look across to determine if any of the row columns are blank. Can you select a range, like [Column1]:[Column100], and determine if any blanks exists without having to do a ISBLANK for every column @row? I've used this in the past for a check box column formula but I…
Copy only specific rows when criteria is met?
I know how to copy rows to another sheet when a criteria is met. But is there way to do only copy certain rows to the new sheet? I can do this via Zapier, but would love to be able to do it natively within Smartsheet.
Count the number of projects done for a client without sub projects
I'm trying to count the number of projects completed for a client. I've changed the client name to Apples for confidentiality purposes. I want to count the number of projects done for Apples, without including the indented rows. Screenshot below: I have tried the following formula: =COUNTIF(DESCENDANTS(Client:Client),…

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