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.

Using Contains If formula
Hello Community, I have a form which can be filled by different fellows. These fellows fill out the form to submit their budget of $ that they have used for different purposes. I am copying the entry from the Master sheet to the respective fellow sheets using automation. Now these sheets have fellow 1 to fellow 20 columns.…
Mid/Find Formula Help
I have this indexed @row info to a column named "AR" (Format will always be like this and no middle name). I need to split First two words to column named "Name", Split email to column named "Email" and split phone number to column named "Phone". FirstName LastName Email@email.net 888-888-8888 Split to 3 columns: "Name":…
Formula
Is there a formula that can help me automate data entry into the placement status column based on Run and Finish date? As per the example below, the row with the upcoming status gets an upcoming placement status because the run date (8/5/24) and finish date (8/5/24)has not been reached. Also, the first column that has a…
COUNTIFS on Summary Sheet for a Given Date
Hello, I am wanting to count survey results for my summary sheet. In the example below I want to count the results after a certain date where communication = "No Concerns". I am receiving an #INVALID OPERATION ERROR when I submit the code below: =COUNTIFS([Evaluation Date]:[Evaluation Date] > DATE(2024, 7, 1),…
Countifs with Distinct?
I have two columns, Column A and Column B. How can I check Column A for a condition, and then count all the distinct corresponding values in Column B for which that condition is met? Is there an easy way to do this with Countif / Countifs and Distinct? Thanks!
What is a formula that will return a cells value located in a table based on two criteria?
So i want to include an interactive element on my dashboard when my client will chose the borrow amount and the term from two dropdowns. Ideally this would lookup the table on the right and return the corresponding value to the pink cell. any help is appreciated
Due this week formula
I am using this formula =IF(WEEKNUMBER([End Date]@row) = WEEKNUMBER(TODAY()), "This week", "-") to pull a report if a task is due this week. The formula is pulling tasks that were completed a year ago and pulling them into the report as due this week. How do I correct this?
Auto Fill Cell with HEX Color from code on different cell
Hi. I have two columns with many rows. "Column A" is called HEX that has a HEX color code that is distinct to that row (this code will not be repeated in the sheet) I want "Column B," called SWATCH, to automatically fill with the HEX code from "column A." I have a formula to do this in Excel, based on pulling from an…
Help with automations
Hi all, I am trying to build a new sheet to track kits we send out. Right now I have it set to autogenerate a request number when the form is filled out. I am wondering if there is a way to get it to duplicate that row for the request number based on the kit quantity selected in the request form that way i can track the…
Can formulas be copied over to another sheet by the automation workflows?
Here's the scenario: I have a scoring template that I intend to use for different site locations to gauge their several programs and their current state. This template has formulas imbedded to calculate scoring for each program. So let's say Sites A, B, & C all complete their template, and these sites are part of Division…

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

NEED TO ADD DAYS TO THIS FORMULA IF ANOTHER CELL HAS SPECIFIC ITEMS OF A DROP DOWN.
I have this formula that works for this cell when information is missing from reference cell A. However now we want to be able to add additional days if reference cell B has specific items checked in a dropdown. I just can't seem to get it to work. Any suggestions. =IF(ISBLANK([ ENGINEER (tentative start date)]@row), "",…
Help with Average Formula
Hi there - I'd like to ask for a hand to calculate an average formula. This is a cross sheet formula where I want to aggregate an average time duration by Assigned To, by quarter. (I also have instances where my time duration is zero - I think that was causing me a divide by zero error). Metrics sheet/destination: Source…
Check a Box if Between Dates on Another Sheet
Hey all, I am trying to build a formula to check off a box on my sheet if at least one date is found between two dates on a separate sheet. I've tried a million variations but here's what I have so far, thinking I was overcomplicating things =COUNTIFS({Approve Date}, >=[Week Starts]@row, {Approve Date}, <=[Week Ends]@row,…

Formulas and Functions

Discussion List

Help Article Resources

Categories

Popular Tags