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.

Cross sheet Formula
What formula should I use to populate column E in "GHG Electricity" adding monthly total (Columns E to P) from cross sheet "SPI Data" for each site (in Column A) with Electricity (in Column C) ? THANKS!!
How to write formula to add few columns in a row from selected rows
How to write formula in another sheet referencing this sheet to add few columns in a row for selected rows ? for example, sum all electricity for all sites e.g. Aizu, Belgium, Bucheon...etc for Q1 (Jan, Feb and Mar). THANKS!
Can i exclude first 25 and last 25 rows from a Column, and also seperate the middle rows to a sheet
I have a Line count of 100 rows in specific sheet, i want only 50 rows from 26 to 75, which intern excludes first 25 and last 25 rows, and also can i seperate the file with the middle 50 rows
IF CONTAINS VLOOKUP
Hi, I'm trying to do the below formula, that if Column 1 Contains "No Match", VLOOKUP Column 2, and get Value from referenced sheet, is that possible? see below. =IF(CONTAINS("No Match", [Column 1]@row:[Column 1]@row), " ", " ",(VLOOKUP([Column 2]@row, {Sheet Name Range 1}, 2 ,false ) Thank you
Nesting IF statements that use the FIND function
Hello all! To preface, i've searched into nesting IF statements on the community and I believe i'm following the correct rules, but my result is #incorrect argument every time. I made this function: =IF(find("This Long Name",[Full Name]@row)=1, "TLN"+MID([Full Name]@row, 17,20), [Course Short Name]@row) The column Full…
Wildcard *
Hello, Needing to know if we can use Wildcards in a countif formula? =COUNTIF({Copy of Projects Range 1}, HAS(@cell, "450-S*")) Looking for formula to count every occurrence of a cell that 'starts with' 450-S Thank you, Glenn
Days until the later of two dates
Trying to figure out how to calculate the number of days from today to the later of two date columns. This will be used to determine number of days until the date in column named "Expirations Date" unless there is a date in the "Extension Date" column in which I would use that instead. Hope that makes sense. Thanks D
Fill in Status based on multiple combinations
Hello, I've been trying to figure out how to automatically populate a column in a column, based on data in 2 other columns. I want the status to populate "Open" or "Closed" based on the information in the Closure Date and Opening Date columns. Blank Closure Date and Blank Opening Date = "Open" Closure Date Present, Opening…
Simple SUMIF
To calculate the Gross Profit per hour achieved by a Supervisor I use this formula and achieve the correct answer =(SUMIF(Supervisor:Supervisor, "John Smith", AGP:AGP)) / (SUMIF(Supervisor:Supervisor, "John Smith", [A_Labour Hrs.]:[A_Labour Hrs.])) AGP refers to the Gross Profit and [A_Labour_Hrs.] to actual hours. I now…
Asking for AVG if specific date criteria is meet.
Asking to average the number of days worked on a project if the due date was after 2020, 12, 31. Something in this is not working correctly. =AVG(COLLECT({Days Total}:{Days Total}), IF({Completion Date}, >DATE(2020, 12, 31)))

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

Issues with Numbering Children (Child 1, Child 2, etc.)
Hi! I am trying to implement a column formula in Smartsheet that assigns an ordinal number to each of the children in a sheet. For example, the first child would get a "1", the second child a "2", and so on. I've tried several options, and the one I liked the most is the following: Create an auxiliary column with a "1"…
Netdays calculation with optionnal date column
Hi i'm looking for a formula to calculate the netdays between 2 dates. but i have 3 dates in the equation. a date column (date 1) whit references from another sheet and a manual one (date 2) to overwrite the first one. the third date is the "today" date (date 3). so i'm looking for something like: netdays between date 1…
Index/Collect from Descendants
In this sheet, I have a column [Groups] which is a parent column to multiple "Groups", each of which is a parent column itself to tasks associated with each group. I need help putting a formula in [Start Date] of the parent row that would give me the very 1st value from [Date] column that is a Descendant of [Groups]@row…

Formulas and Functions

Discussion List

Help Article Resources

Categories

Popular Tags