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

Net Days showing 36500 no matter the actual dates provided.
I am trying to use the NETDAYS function, but it will only respond with 36500. The result should be like 60. The dates I am trying to count are part of some simple (COLUMN + COLUMN) formula.
Trying to return 3 months prior to Date Column
Hello, I am trying to make a column to return dates 3 month before the date in another column. Essentially going to be notifying teams 3 months before something expires. I have a column for the dates. It is set to "date" and i'm using this formula in a helper column =DATE(YEAR(DATE@row ), MONTH(DATE@row ) - 3, DAY(DATE@row…
Move data from a column into a cell
I would like to pull the name that is listed in the Name column into the Primary Column. Note that the name could be listed multiple times in the Name Column.

Formulas and Functions

Discussion List

Help Article Resources