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.

SUMIFS with date range
Hello I am trying to get a sum result based on a date range, but for some reason it is returning 0, any thoughts? =SUMIFS(Amount:Amount, [Date Billed]:[Date Billed], AND(@cell <= DATE (2024, 6, 31), @cell > DATE(2024, 3, 31)))
Symbol Formula for values
I need to create Symbol Column Formula to display the following color symbols based on data in another column: Red symbol If value is in Vacation balance column is>= 175 Yellow symbol If value in Vacation balance column is< 175 AND >= 160 Green symbol if value in Vacation balance column is <160 Help?
Is there a formula to extract the state from an address?
Hi!! Is there a formula to extract the state from the following address? The state should be after the comma and before the zip code number. 12345 Paris Rd Dallas, Texas 75240 United States Validation Status: Validated 9856 SLEEPING BEAR RD SKOKIE, Illinois 60076-1920 COOK United States Validation Status: Validated 333 E…
Create a =COUNTIF Formula for dropdown column that has an IF condition
Hi Smartsheet Team! I need some assistance developing a formula that is going to count multi item dropdown columns. I am trying to create a dashboard widget that will count per Resolution Status by rep. Effectively, having a widget that shows Rep name > Resolved & In Progress (two separate counts under the same rep without…
Text Join Columns with a row range
Hi there- I want to Join collect the Email column(Contact field) and the Palo Alto Additional Emails column(Contact field) together with the ranges of rows 47-49 The first part of my formula worked: =JOIN(COLLECT(Email47:Email49, Email47:Email49, <>""), ", ") But when I added in the second part of the formula it didn't…
Need help fixing formula (single value for multiple selections)
I was working on coming up with a formula that if the user selects either one choice or multiple choices in the same category it will equal the same or highest amount. The sample data I used was as follows: Dog, cat = 3 Mouse, rat = 2 Snake, lizard = 1 The following formula was suggested to me and it does work to a certain…
How can I get a count of new rows created on two sheets?
=COUNTIFS({Created 2}, IFERROR(MONTH(@cell ), 0) = 5, {Created 2}, IFERROR(YEAR(@cell ), 0) = 2024)@cell
Match or Vlookup?
Hi all! I had a question about a formula I'm a little stuck on… For a dashboard showing our clinics information, I added a column to the sheet that feeds into the dashboard that will show which doctor is assigned to that clinic. Sheet 1 - all the clinical information Sheet 2 - dashboard metrics What formula do I add to…
Formulas always there
I have a sheet where people submit new items via a form. When this is done, my sheet includes three columns of set formulas. These columns are not on the form. For some reason they stopped working. What should happen: a new item is submitted, these three columns are included and automatically calculate what I need What is…
Auto-Assigning Project Codes Based on Status
Good afternoon, I have a formula to auto-generate a Project Code based on Approval Status and Department and department. =IF([Approval Status]@row = "Approved", LEFT(Department@row, 3) + "-" + (YEAR([Project Intake Date]@row) + "-" + IF(Prefix@row <> "", Prefix@row, "") + [Suffix Helper]@row + "")) Using the screenshot…

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

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,…
How to Automatically Checkmark a Column for entries that are created within a time-based timeframe
I'm trying to write a formula that will automatically place a checkmark in a checkbox column titled "Today Entry" whenever entries meet the "Created" column criteria of being submitted after 1100 AM the day prior and 0800 AM today on a rolling basis: I've been trying to use the below column, but honestly I have no idea. I…
Calculating the total duration from a date & military time start and end
I have been looking at a lot of information on this site and this is the closest one I have found, however it does not calculate the minutes correctly. For example instead of 8hrs and 45 min it gives me 9hrs and -15min. Formula: =(([On-Site Date]@row - [Notification Date]@row) * 24) + VALUE(LEFT([Time of Crew arrival]@row,…

Formulas and Functions

Discussion List

Help Article Resources

Categories

Popular Tags