Formulas and Functions

Archives for November 2020

Formula to report total tasks and total tasks completed
Hi, I'm looking for some help with the below. For the most part I'm able to make the formula work, however I'm unsure how to account for Friday. For example today is Monday, but the formula only reports Sundays data (nonworkday). Also all of my formulas are in the Sheet Summary, as I'm trying to prevent any need for…
Average Number of Bids and Average Bid Price per Unique Bid ID
Hi, I'm trying to get the average number of bids submitted as well as the average bid price per unique bid number. Can someone please help me with this formula? The bid numbers will sometimes have letters in them so they won't always be only numerical. Thank you,
Return minimum result from multi options not first
Hello, I have 2 sheets collecting data, both sheets use a unique PRF reference number (which I use for match purposes), I need the look up to return the earliest date from the Book 1 Range 1 data, where the PRF numbers match more than once on the sheet. Currently it will only return the first option it finds, as you can…
Sending Weekly Email with a Filter in Contacts Column
Hello, I'm trying to find a way to send 1 weekly email to each person in a column with a list of all rows that are assigned to their name. I've looked at setting up an automation, but will this send 1 email per row with their name on? And will you have to set up a new one for each contact? Thanks, Eddie
Create cell reference based upon horizontal axis position
Is there a way to create a cell reference based upon a horizontal axis? For example, for a given column, I want it to always represent the cell directly to its left. For this example scenario, a new column will be added for every date and the new results will be entered into that column - Since the new date column will…
Using ISBLANK with schedule health function
Hello, I'm using the below formula to calculate schedule health. The schedule delta is based off calculating the difference between columns Target Start Date, Target End Date and Actual Start Date, Actual End Date. =IF([Schedule Delta (%)]@row > 0.25, "Red", IF([Schedule Delta (%)]@row < 0.1, "Green", "Yellow")) It works…
Does CONTAINS work with TODAY()
This formula is returning false even when TODAY() is in the value in the [Date1]@row cell. =IF(CONTAINS(TODAY(), [Date1]@row:[Date3]@row), "true", "false") The range [Date1]:[Date3] are formatted as a date columns. Does CONTAINS work with dates?

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

Formula help for INDEX MATCH. Or is it INDEX COLLECT?!
I have a holiday sheet for Team Members to log their holidays: Assigned To, Start and Finish Date. A formula has been created to flag the status of when they are ‘On Holiday?’: eg, ‘Holiday Complete, ‘On Holiday’ and ‘Holiday Booked’. Based on this status, an ‘Availability’ symbol also appears Id like to use this…
How to add multiple costs from dropdown menu items
Hello, I am struggling to identify a formula that can read multiple selections in a drop down column, find the corresponding selections and prices on another sheet, and return a total cost. The sheet supports samples that are analytically tested. The user chooses which tests they want to conduct for each sample on the…
Time Elapsed to Today in Months/Years
Hi! I'm trying to figure out how to use the NETDAYS formula to show how long someone has been a member of a certain team. I can't get it to measure to today using the formula that the website suggests. I want it to be running so that whenever I log on I can see the time elapsed to the day I'm looking at the sheet. Can…

Formulas and Functions

Discussion List

Help Article Resources