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

Is there a formula to update the status based off the % complete?
i.e % Complete is = to 0% then Status should equal not started, if % complete is > 0%, then status should be In Progress, if % complete is = to 100% then the status should equal Complete
Hello! I am trying to make a graph with these dates!
I am using a Grid Sheet and a reference sheet but can not figure out how to get the formula to match. I am using =COUNTIFS([Start Date]:[Start Date], AND(@cell >= DATE (2021, 1, 1), @cell <= DATE(2021, 12, 31))) This is my reference sheet formula =COUNTIFS({OnBoarding Partner Agreements Range 1} [Start Date]:[Start Date],…
Formula for numbering rows based on specific conditions
Hello! I need to create formulas for two columns that number rows based on specific conditions. I have a helper column (Number). First, I want to number rows that: Type is "Focus Group Volunteer" and Internal is "yes." Second, I want to number rows that: Type is "Focus Group Volunteer", Internal is "no", and Patron Type is…

Formulas and Functions

Discussion List

Help Article Resources