Formulas and Functions

Archives for October 2022

Countif by month if due date is next month
Hello Community, I am having an issue with my formula. I am trying to give my client a SLA timeline on when quotes are due and overdue on a month-to-month basis. Currently my data sheet is counting the quotes per month and if we get the quote in within XX number of days then we get a complete. The issue is if a quote was…
How to Pre-Populate a Field in a Form?
Hello Smartsheet Community, I am trying to pre-populate my Work Order ID field in a form with the number it is assigned in its row. A while back, we were given a complex formula to include specific fields but it is very complex and hard to understand. Can someone please let me know how to add the Work Order ID to the form…
Date Expected Error or decimal return
I'm trying to return a date value based on the week number identified. So if I have "Week One" in my training date number, I want the Date column to return 10/17/22. I'm trying to use an IF formula to do this but keep getting #DATEEXPECTED error. When I try to convert the column type to text/number instead, it gives me a…
How to automatically check a parent row when children are checked?
Hi, I have 3 level parent-children rows. I also have a column with a checkbox. Is there a formula or automation that would check the parent row if all children are checked?
COUNTIF Formula that counts cells with multiple drop-down values
Can this be done?
Calculate how many days to due date
Hi, I'm new to smartsheet formulas and need to calculate how many days until a item is due using the due date.
Formula adjustment to add percentages of completion
Hello! Currently I am building a formula to identify, based on a Partner's current tier, how close a Partner is to achieving the next tier by obtaining the total number of certifications needed to advance. Example: Partner X is currently "Registered" tier which is Level (LVL) 1. In order to advance to the next tier they…
Formula that sums hours where a single value matches a value in a multi select list AND month
I am working on a formula that calculates hours worked where a member exist in a multi-value list and the month. The detail is recorded in Run Data Sheet. Each record provided the date of the run, start and end time of the run, hours (difference between time in and time out), multi select list of the members that were on…
How to create date picker in Smartsheet?
Hello everyone! Is it possible to create a date picker in smartsheet? - I am attaching an example below. I want to have one column where I can quickly choose a date. I know that it is possible to create it in excel, but I can't find guidance on how to do it in smartsheet. Thank you so much for any help :)
Automate INDEX/MATCH or VLOOKUP the first date of the week each week.
I want the "Week of" column to automatically show the date of the first day in the current week each week starting with Monday as the first day, and if "Week Of" and "Project Name" both match on another sheet where the data is being enter then I want to return the next 4 columns for the rows where the "Week Of" and…

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

COUNTIF by Month in Date
I've always struggled with calculating total count by Month; i can do it for every else but when you throw in dates, it throws me right off. Essentially what i am trying to do is calculate on another sheet the amount of travel done within the month of March This is the formula i am using, but it comes back with 0…
Sum if match formula
Hello, I am looking for a fairly complicated formula (I think). I have two sheets: 1) IW POD and 2) Root Bushing Repair Tracker on sheet 2, I have a column named "Actual Execution Hours" In that column, on sheet 2, I need to sum the hours on sheet 1 in column "Total Actual Hours" IF these two columns match on both sheets -…
Formula working for one data value but not another
Hi Community, I have a sheet that contains, among others, columns titled "Priority" and "Contract ID". Multiple rows can have the same Contract ID and the same Priority. Priority values are 1-2-3 and restricted to dropdown list values only. To collect the number of unique Contract IDs that are Priority "1", I have this…

Formulas and Functions

Discussion List

Help Article Resources