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.

Weeks Out - Production Timeline Templates
I'm trying to replicate a function that is helpful for creating production timeline templates, that I use in google docs. Column A is the due date of a task. Column B is the number of weeks out from your launch date. Column A is calculated by taking your launch date and subtracting the weeks out, to create a due date for…
Auto filling a Formula
I am using a formula to do a COUNTIFS based on Dates, a drop down YES/NO column and a name. I am wondering if it possible to auto fill the name portion of the formula from another cell. The formula looks like this: =COUNTIFS($Date:$Date, <=DATE(2017, 6, 30), $Completed:$Completed, ="Yes", $[Assigned to: Editor]:$[Assigned…
COUNTIF Checkbox Marked and Then COUNT Product
There is a column called "Terminated" and it is a checkbox and 5 other columns with Product1, Product2, Product3, Product4, and Product5 which are also checkboxes. I need a formula to COUNTIF "Terminated" is Checked, then COUNT the Product1, Product2, Product3, Product4, and Product5 columns. For Example: Terminated =…
using IF together with AND
Sorry in advance if this has been answered but I have searched and cannot see the answer but I am sure its simple! I have target finish and actual finish columns, in the status column, I want to report "in progress", "overdue" and "Complete" I have got "in progress", "overdue" to work by the following: =IF(TODAY() <…
Vlookup with cross sheet reference
Can someone please provide an example of the Vlookup formula with the cross reference? I have tried both formulas below and get errors on both. =VLOOKUP([New Job Code]1, {Lot Status Report - Goldmark Range 3}, {Lot Status Report - Goldmark Range 3}2, false) #UNPARSEABLE =VLOOKUP([New Job Code]1, {Lot Status Report -…
If statement based on date
I am trying to create a formula within my "Academic Year" field so that if the "Date" is more than August 25, 2019, the Academic Year field contents will be "2019-20" -- otherwise, the field contents will be "2018-19". =IF([Date]1 > (2019, 8, 25), "2019-20", "2018-19" I am using the date format (2019, 8, 25) based on what…
COUNT by Manager and by Product
I have a column called "Manager" and Product columns called Product1, Product2, Product3, etc and these are all checkboxes How can I do a count by Manager of Product? Example: Joe Smith * Product1 - 3 * Product2 - 10 Jennifer Johnson * Product2 - 12 * Product3 - 5 * Product5 - 10
Formula counting State
I have a column called "State". How can I do a count by all "State" and produce a chart? I want to avoid doing a formula for each State. Suggestion?
Value between dates
Hi Everyone, New smartsheet user here, I am trying to get a total cost between dates on a running construction schedule. I have different row-categories (Land acquisition, permitting, site work, construction, & selling) with columns showing start date, end date, and cost. All costs are positive, revenue (selling) is…
SUMIF formula with range
=SUMIF([Total Sales of Goods and Services]@row :[Total Sales of Goods and Services]@row, [Payments in Quarter]@row = "Q.3.18")) The purpose of this formula is to sum all totals of Total Sales of Good and Services if the Payments were made in Quarter 3, 2018; I'd like to make this formula workable per selected quarter for…

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

Get a Countif on values in a multi-select column of cells
I am trying to write summary formulas to determine how many values appear of each in the drop-down of a column. I tried adding a wildcard, I have tried HAS, and tried CONTAIN, but I'm still not getting the right values. Currently I am getting 0 as a return value on this formula: =COUNTIF([ROI Type]:[ROI Type], HAS([ROI…
summaries in report
Hi i'm trying to build a report with summaries options. i have two column that i want a Min and Max summary. the column for the Min i a texte column and works well. but the other column has formulas and the Max doesn't works. there is a way to fix that? (instead of creating another text column in the source sheets?) here…
Converting email addresses with hyphenated First and/or Last Names
I am trying to convert email addresses into a Name field with the employee's First and Last Name, capitalizing the first character of each part of the name and keeping the hyphen in any part of the name that has one. I started with hyphenated first names so taking shmoe_joe-bob@company.com and converting it into Joe-Bob…

Formulas and Functions

Discussion List

Help Article Resources

Categories

Popular Tags