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.

countif and contains statement for extended drop down menu
Hi all, I would like to reach out to the community to help me solve my (probably simple) issue I am facing. To provide you with some context: I am building an online database to enable tracking all the requested amendments and to gain inside into invoicing on a small scale. This database is created for different layers to…
Need to return a count of dates from last week in the current year
Hello, I'm trying to return a count of how many dates in a column were from last week in the current year. I was using this formula =COUNTIF([Date Step 3 Completed]:[Date Step 3 Completed], IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY()) - 1), but I noticed that it started pulling last week's count from 2021 and 2022.…
Extracting a date from a line of data
I have been working on formulas to automate data extraction from a string of data that is used to identify lines of data being exported from equipment and I am struggling with how to extract the date and convert it into a regular date format. Any help would be appreciated! Example line of data: A3146 GAT 26MAY22 CCV21 The…
Formula to return correlating value and column name
I've been attempting to construct a formula to find a specific value within a sheet then return it's correlating monetary value along with that value's column name. For instance in the screenshot below I would like to search for the number 7 then return Sep $8. If I have to search for the two separately that is fine. Is…
How do you tell a cell what to do based on being a Parent or Children row?
I would like to use one formula in the entire column instead of having to copy and paste based on being a child or parent row. My child formula looks for word vacant in the name column and if so subtracts the total of 4 other cells from 0. If the word is not found the cell remains blank. =IF(CONTAINS("vacant", Name@row), 0…
Can a form field require a specific number of digits to be entered
Is there a way (similar to Excel data validation) to require seven digits to be entered into a forms field? Each individual completing the form has a unique ID number which is seven digits long. Currently the first three or four number of the ID number are zeros. Each time an activity is completed the individual completes…
Using COUNTIF together with a date range
Hello, I'm trying to count how many rows meet this criteria (use a cross sheet reference): Tier is Experiencia Lite (or whichever tier I need to count) and SLA was signed between 1/1/22 and 1/31/22 (will be doing this for each month) This is what I have but iI get an unparseable error: =COUNTIFS({Tier}@row,"Experiencia…
Project Portfolio - Health Question
Hello, I am currently utilizing the PMO Template Set. I would like to incorporate a metric into my dashboard that shows the total health of all my projects into one metric. I am currently doing this on my individual project dashboards utilizing the red, yellow, green color balls screen shot below. If I am managing roughly…
Email Formatting Issues
Hi all, I have setup an alert to notify certain people when a new line in a sheet is created. The problem is, smartsheet sends an email with white text highlighted in yellow. It's impossible to read what's going on with the row in question. Is there a fix for this?
Automating Summing Daily Data to Generate Monthly Total
Hello, I have a spreadsheet with data different days of each month. I need to sum the number of data so that it provides a total for each month. For example, for the month of June, I will need to sum all incoming 30 data for each day to generate a monthly total. How can I do this so that the result is generated…

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