Formulas and Functions

Archives for October 2022

Count Compliant Status Reporting
Hello Community, I am pretty stumped on this one; I want to count the number of rows (projects) that are in and out of compliance for their status reporting ("Status Date") based on their project stage ("Project Stage") as different items in the Sheet Summary. Currently, "Project Stage" has multiple possible stages, but…
Auto populate Address from Address ID
Hello! I have a sheet that takes in a form for different build sites. I am trying to figure out how to auto populate an address from and address ID that’s input from a form. The address would be associated to this address ID from an Excel sheet containing all the information. I was thinking of pulling the address (and…
Average for Filtered Children Rows Only
Hi! I have sheet with a column Y or N that I filter on. I filter on all the Y and am trying to get the average for the children rows (they are a %). However, it's averaging all the children rows (not taking the filter into consideration). How can I get the average for ONLY the Y children rows? Thanks!
Index Match (No Match)
Im pulling in information from another sheet to the sheet in the image below and I cannot figure out how to only make the date show up when there is a match. I do not want "No Match" to appear. =INDEX({picked up}, MATCH([Ticket #]@row, {ticket number}, 0))
Manual Override of Formula Index/Match for Out of Office with Column Formula
Hi All, I have an index/match pulling in data relevant to a Business Unit from off sheet. I need to get it so that I can override this formula data to be blank/allow entering of a different email address by the sheet user. What I have setup already (working with Column formula): 1) User inputs Business Unit (BU) 2) BU auto…
Possible to use cell linking for formulas?
I have multiple sheets that are set up in the exact same way, used by various team members. Each sheet has several formulas. If there is an update to the formula, I have to go into each sheet and adjust it. Is it possible to have a reference sheet that houses all the formulas and use some kind of link to reference the cell…
Calculate Supplier Information
Hello, I'm stuck with a formula...I am trying to calculate the number of suppliers with open complaints greater than 3. Any ideas? Thanks, Kelly
What formula should I use to convert drop down text responses to a corresponding number?
We are working on an assessment survey that has text dropdown values. Each of the text responses will be scored with a different numerical value. I have created a corresponding column to capture the score for each of the questions but am having trouble with the formula. For example, the respondent will answer question 1…
AZ Time Zone Issues when form is submitted
Working out of AZ pacific time, I have analysts submit forms at 9:00pm Saturday night and the form is counting the audit for the following week. Example: Week 44 audit is submitted on or after 9:00pm Saturday night, form is counted on Week 45 section, and not for Week 44. All the analysts personal settings are set to AZ…
Getting the $ to show up in a dashboard report
I have a dash board report that for some reason is not showing the $ on the report. It is set to US Currency in the metric page and on the report but will not show up that way on the report. It is doing it on others and I am not quite sure why. Can anyone guide me on this issue?

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions

Date between dates to return the date in cell
Hello, I am struggling with one of my formulas: =IF(AND([Start Date]@row, >=DATE(2020, 10, 1), [Start Date]@row, <=DATE(2021, 9, 30)), [Start Date]@row, IF(AND([End Date]@row, >=DATE(2020, 10, 1), [End Date]@row, <=DATE(2021, 9, 30)), [End Date]@row)) If the start or end date falls between the dates in the AND statement, I…
How to create a stacked column chart from a report
Hi everyone, I'm trying to create a staked columns chart in my dashboard to show per Assigned to the number of cases per status. However, the chart shows up just as 1 big column. Would someone knows why? Please see the photo for visual. Thank you in advance for your help
Linking Parent and Child Rows with a Unique Identifier
Looking for a way to tie Parents to Children with a numeric value, so if they become separated and lost in a sort, they can be easily found and nested under the correct Parent again. We have an automated [ROW ID] column to reference. We created a [SLIP ID] column and used =PARENT([ROW ID]@row), and it does populate the…

Formulas and Functions

Discussion List

Help Article Resources