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.

unwanted break in formula which references another sheet
I use this formula to combine mutlipe dates and notes into a single string to display them by month. the dates/notes are in a separate sheet. the formula example is (where 'Davis October' changes depending on the last name/current month) ="October " + Year1 + CHAR(10) + IF(COUNT(DISTINCT(COLLECT({Davis October}, {Davis…
Formula to check a box when specific criteria are met
Hi! I am looking for a formula to check a box when specific criteria are met. I have multiple columns that have dropdown options. I would like the box to check when each of those columns have either "Approved" or "Declined" selected. But if "Submitted" is the option shown, then it should not check the box. So all…
User Email in Column instead of Name
Is it possible to get the Smartsheet user email in a column in addition to or instead of the user name? I'd like to be able to do a lookup on the email address. The email address is relatively consistent among various applications and names are not consistent. (I did search, but since the changes to help and community were…
Contact List - Extract First / Last Name?
Is it possible to target the first or last name of a contact inside an alert email? So e.g. if I have a contact called "John Doe" in a [Contact] column, and I send an alert with Dear {{Contact}} I will get the full name. Is there a way to only get the first name?
COUNTIF Function
Hello, How do I calculate the number of "completed" tasks in Phase 1 only? I figured out how to calculate the number of "completed" for my whole sheet but if I copy that formula to a new column in my metric sheet and highlight just the status column cells in Phase 1 then my whole sheet value also changes to that. This is…
SumIfs function with a date
Hello! I have a sumifs formula that works great: =SUMIFS({Amount}, {Branch Number}, Label@row, {Request Type}, "Maintenance") My problem is when I try to add a date aspect to this formula. I want to break this number down by month, so how much was spent at a specific branch by request type and month. I added this formula:…
Cross Sheet Formula Help - SUMIF, SUMIFS, OR - Which to use?
I'm trying to create a cross sheet formula that will track the dollars spent on items in various stages. I've easily created formulas for two separate stages, "Pending" and "Requisitioned". I'm struggling now to create a cross sheet formula that encompasses everything past "Purchased". This includes "Received", "In…
Help with If formula using Icons
I need your help to show status as Yes, No, Hold using this criteria: No - 90% + Hold - 51% - 89% Yes 0% - 50% This is my current formula, where the 'Yes' and 'No' icons show up as intended, but those that fall between 51% - 89% are showing the red X icon for No, not Hold. =IF([Resource Utilization]@row > 0.9, "No",…
Reference Sheet Summary fields in formula in sheet
I need to create a formula that concatenates (JOIN or "+") several fields in my sheet summary, back into a column in my sheet. What is the syntax I need to use to be able to reference sheet summary fields? Thanks!
successor and predecessor between two project plan sheets
In my project there are 5 sheets in which some of their activities are linked to each others success. Is there any formula which I can use to define successor and predecessor between multiple sheets. Please advise

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

Can anyone tell me what's wrong with my formula?
I'm trying to set up a project plan that has color indicators for the timeline: RED=past due, Yellow=due in the next 5 days, Green=due in the next 14 days, Blue=due later. =IF([Due Date]2 < TODAY(), "Red", IF(AND([Due Date]2 >= TODAY(), [Due Date]2 < TODAY(+5))), "Yellow", IF(AND([Due Date]2 >= TODAY(), [Due Date]2 <…
Help with a #UNPARSEABLE error
I want to reference the values in a column from another sheet (2025 IOE Procedure Assessments Equipment). And if any of them match any cell from the Equipment column from the sheet I'm working in then, I want the Audit on Equipment Completed column to return a "Completed" value. This is my formula. What is wrong with it?…
I have the Year and Month on a sheet. How can I have the summary look at both to count?
I would like to create a summary if the month is Sept AND the year is 2024 to count. I have found that I can use the and but keep getting an error message This is the Formula I have in the Summary that is coming up #Unparseable =COUNTIFS([Next Renewal Year]:[Next Renewal Year], "2024") AND(([Next Renewal Month]], "Sept")

Formulas and Functions

Discussion List

Help Article Resources

Categories

Popular Tags