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

Upcoming Events

Brandfolder Collaborator User Guide
Get ready to seamlessly upload and organize content within your Brandfolder! This on-demand session is designed to help you become a confident contributor and collaborator, ensuring your team has the right assets at the right time. What You’ll Learn: Maximize Features in the Asset Modal: Learn to navigate the asset modal,…
Brandfolder Admin User Training
Managing digital assets doesn’t have to be complicated. Join us for this two-part, on-demand webinar series designed to give Brandfolder administrators the confidence and skills to create smooth, efficient workflows. Whether you’re just getting started or ready to optimize your setup, these sessions will help you unlock…
Smartsheet Customer Success Onboarding (Japan)
Smartsheet カスタマーサクセスオンボーディングは、Smartsheet についての基本を学びたい方向けに毎週開催しているインストラクター主導のトレーニングコースです。ウェビナーは、始めるための基本、共同作業、およびさまざまな方法での作業の視覚化の基本など、Smartsheet のご利用を開始するために必要なすべてを網羅しています。

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions

Average by section within a column
I am working on a sheet where we are tracking the percent complete within a column. The top percent complete formula is =AVG([Percent Complete]2, [Percent Complete]17). The formula in [Percent Complete]2 is =IF(Completed@row = "Completed", "100%", COUNTIF(Completed3:Completed16, "Completed") / 14) The purpose of the…
How to return a blank when searching for max date and one of the dates is blank
I'm searching another sheet date column for the max date where two number columns (CID) are equal. It works fine, but if the list of dates contains a blank, I want the formula to return a blank instead of the max date it finds. =MAX(COLLECT({DAFD}, {CID}, @cell = [CID]@row))
How do I get the exact number of Years based on the date column (no more rounding up)?
Hi there! Can anyone kindly please help me with this - I'm new to SmartSheets and having trouble working out formulas… In one column I have dates that Volunteers started volunteering with our company. The second column I used the following formula to get the number of years that it totals: =YEAR(TODAY()) - YEAR([Vol Start…

Formulas and Functions

Discussion List

Help Article Resources