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.
Discussion List
Average of lease $ amounts in a certain region
I would like to see the average lease amount per region. I am cross sheet referencing. The formula for the average works. But when I add in the second part (countifs) to make sure a PE GL Checkbox has been checked and to specify a region (I do a cell reference) it doesn't seem to work. For instance, in 1 region I do not…
Formula from hell
I have ranges of data in multiple forms. There is criteria that must match from each to be summed on a third sheet. The criteria are GL Accounts and a month Code. One of the month codes is listed as 12 Month. The intent of that code is to signify that the cost in one of the columns should be spread across 12 months. So I…
Assigning a date to a value
I'm working on a gantt chart that is tracking teams progress on a project using agile development. Business Case / Background: When the teams provide updates to when a task is going to be completed, they reference the name of the sprint we're in. So for example if "Task ABC" will be completed in Sprint 1A that means its…
CountIFS- Name + Status
I am trying to enter a formula that will count when the name Kara is listed and the status is listed. I keep either getting an error or it will double count. Please help. The end goal is to find ou how many each person has in each status listed. Example: =COUNTIFS([CSMNAME]:[CSM NAME], "Kara", Status:Status, "Locked")
Nested IF/AND
When I check Declined Date, it is not changing to "Declined". Can someone help? =IF(AND(NOT(ISBLANK([Submit to Design Agency]8)), ISBLANK([Submit for Approval]8), ISBLANK([Approval Date]8), [Declined Date]8 = 0), "Submitted to Design Agency", IF(AND(NOT(ISBLANK([Submit to Design Agency]8)), NOT(ISBLANK([Submit for…
Formula to count instances of a word
I have a sheet that is referencing another sheet to provide counts on instances of certain values in different columns. What I'm stuck on is counting how many times a single word is used if other text is in the same cell. For example if I have a column call Fruit and I want to know how many cells contain the word "apple"…
Alert Rules
Is it possible when more than one row having same value( duplicate row is submitted) an alert message goes to the user that row already exist something like this..?
RYG Automation using Drop Down lists
Hi I've watched a few videos on RYG automation based on due dates but is anyone able to help with the IF function using categories from a drop down list instead? I.e. Looking at Recruitment Status: If someone chooses 'On Hold' from the drop down, I want the RYG column to show Red. If someone chooses 'Interviews and…
Sorting a sheet when opened
Wondering if there is a way to sort rows of a sheet on open. Otherwise you have to set this up manually each time. If this is a smart sheet you would think it would be able to do this.
Combining AND with OR
From a logical perspective, I'm trying to write a formula that says the following - If the (Issue Type = 'BUG') AND the( Status = 1 of 5 possible values), then the cell value should be a red ball, otherwise, the cell value should be green. Regardless of what I try to type, I'm either getting #UNPARSEABLE or #INCORRECT…
Help Article Resources
Trending in Formulas and Functions
How do I summarize text data from multiple cells into one cell only if criteria is met in each cell?
I am sending a form out to associates in which I ask them to rate their familiarity with multiple subjects. The 3 available choices for them to choose from are: Very Familiar, Somewhat Familiar and Not Familiar. I would like to summarize their responses from multiples cells (Sales Orders, Invoicing, Stock Status, Purchase…
Multiple Criterion to Display Value from 2 sheets
Hi! This is my first time asking some question here I'm having trouble on coming up with a formula for what I want to do. I have two sheets. 1st sheet contains MONTH column, YEAR Column and INVOICE VALUE Column 2nd sheet is where I will be putting the INVOICE VALUE to each month of the year. They both have Row ID so that I…
Help please
I have a column that keeps returning errors….causing quite a bit of confusion and frustration. The first threat formula is working as intended. First milestone threat formula: =IF(AND([Percent complete]@row <> 0.1, TODAY() > [Currentmilestone date]@row +14),"Red", IF(AND([Percent complete]@row < 0.1, TODAY() >…
