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.

OFFSET calculation
Hello, I am wondering if I could have some help on a calculation matter. As I have tried to find quickly some answers on the forum, it seems that you can not use the OFFSET function from Excel in Smartsheet. Right now I was using it in column H to calculate the stock of the compounds in the inventory. The goal is to add…
COUNTIFs referencing 1 dropdown column and 1 date column
I am using the formula below to find the number of times a particular service was requested in a given year. I am referencing columns from a sheet called Sheet1. =COUNTIFS({Sheet1 Service Requested}, "Isolate Requested", {Sheet1 Date of Completion}, YEAR(@cell ) = 2021) The column Service Requested is a dropdown with…
Count Unique Entries Multiple Criteria
I would like to count the number of unique employees who have submitted an entry per quarter. Here's the formula I tried: =COUNT(DISTINCT(COLLECT(Employee:Employee, YRQtr:YRQtr, YRQtr@row, [Idea Approved/Denied]:[Idea Approved/Denied], "Approved"))) Always returns a value of 1.
IF statement based on cells in same row
I have a sheet where every row requires two approvals before it's fully approved. What would the formula be to make this work? For example, approval column 1 is named "initial approval" and approval column 2 is named "COHS approval". Both cells are dropdowns with the options pending, approved, or denied. I want the first…
Need to add an or condition to Count/Collect Formula in Project Metrics Sheet.
I need to modify below formula to collect Project ID's on intake sheet where Project Status is "In Progress" or "Not Started". Below works only if Project Status is "In Progress" =COUNT(DISTINCT(COLLECT({Project ID}, {Project Status}, "In Progress", {Project Length}, Label@row)))
Start and End Date Number of Days Calculation
I'm having trouble calculating the number of days between two dates. I'm receiving the message "INVALID DATA TYPE" and using this formula =NETDAYS([Start Date]@row,[End Date]@row)
Formula to average scores from form responses
Hello, I am building a repository for Interview Evaluations. I have created a form to collect answers from the interviewers to rate candidates. Interviewers are asked to score candidate competencies in multiple areas. I have assigned point value to each answer (2 points for Strongly Agree, 1 point for Agree, 0 for Neutral,…
formula
I need a formula that reflects the remaining due if not paid in full isnt checked off otherwise reflect it as a $0.00
How do I correct a #NOMATCH error when the =VLOOKUP formula worked in the initial cell?
I used VLOOKUP to reference my feeder sheet for two references. the formulas work for the first cell, but only when i use the data in the first cell of the referenced sheet. There is a Form attached to this sheet so when information is submitted, i want the sheet to autofill missing information from the reference sheet. I…
Using NOT with JOIN/COLLECT
Hello Everyone! I am creating a formula that returns the ID if the item type is "Book" and the status is NOT "Sold". My current formula I was trying is: =JOIN(COLLECT({ID}, {Item}, "Book", NOT({Status}= "Sold"), CHAR(10)) What is the correct way to return the ID for an item that is "Book" and the status is NOT "Sold". (Or…

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

Help with revision of Nest IF Statement for Symbols
Need a revised formula, which is in the Certification Complete column. This worked great. However I was just made aware of something. Okay, @dojones provided a perfect solution. However, as we are all aware, we are not always told about all the pieces of the puzzle. that solution was: =IF(AND(ISBLANK(Certify@row),…
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…

Formulas and Functions

Discussion List

Help Article Resources

Categories

Popular Tags