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.

Email Comparison Formula Needed
Hello Community, I am looking for some help with an email IF Statement and a formula to more information, when a match is detected. I have a couple sets of 3 columns each: Email, First, Last and I need to compare Email with other sets of Emails. NOTE: The 2nd or 3rd or 4th set of Email, First, Last will not have First and…
Dynamic Status
A more refined and slightly more universal solution to the first posting is as follows: =IF([% Complete]18 = 0, "NOT STARTED", IF(AND([% Complete]18 > 0, [% Complete]18 < 1), " IN PROGRESS", IF([% Complete]18 = 1, "COMPLETE", IF([% Complete]18 > 1, "INVALID ENTRY", IF([% Complete]18 < 0, "INVALID ENTRY"))))) This assumes…
Smartsheet relational database
Hi all, Im wondering if it is possible to set up a relational database using smartsheet and cross referencing other tables (which it looks like it is), however, i'd like to also be able to query these tables for specific data. Is there a way to query linked sheets or does anyone have suggestions for tools to use in…
How to combine 2 Countif queries where 1 is a date in the next 30 days
Let's say I have 2 columns: Widget Colors and Order Date. I'm looking for a formula where I get the number of "red widgets" that have an order date in the next 30 days. I have tried this formula without success: =COUNTIFS({2019 Widget Reporting Range 1}, "Project") AND ( {2019 Widget Reporting Range 2}, <=TODAY(30))
Index Match issue
Having issues with the Index Match formula. Formula is successfully pulling information from the correct column on the referenced sheet that I want, but it is defaulting to only pulling the last row of information instead of the line that aligns with the name I would like. My formula is as follows: =INDEX({RRA Range 8},…
Multiple IFS for calculations
Hi, I have no problem doing this in Google Sheets but I'm not sure how to do this in Smartsheet. I have columns that contain THC and CBD content, measured in a number of different ways, including percentages, mg, percentage ranges, etc., but they're always consistent units of measurement across the row. I need to calculate…
Formula results varying between today and yesterday
I have a formula pulling info from another sheet. This then feeds into a dashboard metric. The formula is a COUNTIF for anything with today's date. What is happening (and this sounds crazy....I had to get a couple of people to observe it to confirm it wasn't just me) is that at some moments in time the figure on the…
Flag out of Range Date
Hello, I have a date range: Date Range Start & Date Range End, and I want to flag it if the "Actual Completion Date" is before the Date Range Start OR if the "Actual Completion Date" is after the Date Range End. Can anyone help? I've tried: =IF([Actual Completion Date]@row > [Date Range Start]@row, OR([Actual Completion…
auto update from multiple sheet
Hi, i'm looking for someone who can help out in setting up smartsheet for the company.
Count only outstanding items
I have a Request Management sheet with a Form that we used to track tasks. They template came with a Metric sheet that is very useful. One of the metrics on this Metrics sheets counts items by assigned person. I want to update that to count only items that are not Closed status. Current formula is: =COUNTIF({Request…

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