Want to practice working with formulas directly in Smartsheet?
Check out the
Formula Handbook template to view 100+ formulas, including a glossary of every function and examples of commonly used and advanced formulas.
Product questions?
Ask it here! The community's got your back.
Discussion List
Mod function substitute
I am creating a due date field that will auto update for a Dashboard. The goal is that every 2 weeks an updated date will be shown. From what I can see MOD is not supported in Smartsheets. I am wondering if someone is able to help me with a mod substitute on a date field like I have done in Excel. The formula in Excel is…
Work Column
Hi all - I'm trying to add a "work" column similar to that of Microsoft project. Specifically, I'd like the work of the child tasks to roll up to the parent tasks: Parent task 1 (3 - Sum of child tasks) - Child task 1 (1 hour) - Child task 2 (1 hour) - Child task 3 (1 hour) Is there a way to do this?
CountIF between 2 dates and Notblank cells
Hello community! First post here I need to count the number of cells in a separate column per month. Example: March: 13 April: 10 Ok, what I've already tried: =COUNTIFS((Criado:Criado); @cell = DATE(2018; 3)) Returns: 0 counts Expected: 20 counts =COUNTIFS([Título da ideia]:[Título da ideia]; NOT(ISBLANK(@cell))) Returns:…
Automated RYG Balls
Greetings Smartsheet Community! I am working on a formula to automate the RYG balls in a status column that references data in the cells of 3 columns, and it seems to be working well. I am wondering if there is a way to add to the formula so that the status column stays blank until the data in the reference cells is…
CountIf Help
Is there a formula that would count the number of cells where a Done check is not completed but only when another columns due date is in the past? The goal is to count how many past due tasks I have. Does anyone know the best way to accomplish this?
SUMIF Trouble
I am trying to use SUMIF to add the dollar amount for jobs that have 100% probability. I wrote this formula: =SUMIF(Probability:Probability, "100%", [Quoted Amount]:[Quoted Amount]) and it returns a 0 but no error. Also all of the data in this sheet is linked from another sheet. Is the problem because the data is linked?
Help on Formula: automate status based on dates
Hi all, I'm pretty new with formulas and I wanted to automate the status of each projects based on dates. I have columns Status and Progress. The status column have a selection of: Completed, Active and Not Started and the progress column have: On-time, Delayed and Not Due. On the screenshot below, the columns Status and…
Formula driven by date
Hi All: I only want this formula to activate only if it is greater than or equally to today's date, but having trouble adding TODAY part of the formula: =IF([% Complete (Actual)]3 > [% Complete (Planned)]3, "Up", IF([% Complete (Planned)]3 > [% Complete (Actual)]3, "Down", IF([% Complete (Planned)]3 = [% Complete…
COUNTIFS Formula Help
As one of the pieces of logic in a formula I'm attempting, I want a count within a range (column) if the date (formatted as MM/DD/YY) falls within the year 2017. What do I need to change in the formula as shown in my screen shot?
VLOOKUP Calculation Lag
Hi, I am having problem with a VLOOKUP process. Our current process flow is as follows Import data into a "DataDump" sheet through API, at this point each row of data is assigned a number, matching that of the row number (we do this so we can use an index match formula to pull data through to the secondary sheet we call…
Help Article Resources
Trending in Formulas and Functions
MIN/MAX with Date and Time (Modified Date Column)
Hi all, This should be straightforward but I am stumped! How do I get the earliest (or latest) value including time from a Modified Date system column? e.g. I can get the date if I define my output column as type Date and use a simple MIN/MAX but this means the time is ignored. I thought about breaking the time and the…
COUNTIFS a the number of instances a number appear in a column
Good Evening, This should be pretty straight forward but I can't seem to figure out the proper formula, I keep getting the Unparsable. I would like to count the number of instances 0 appears in the No Activity Column. Please see below the Column I am referencing from and any suggestion is much appreciated.
Formula with symbols when a cell is NOT blank
HI Everyone, I´m looking for help with the following situation. I want My "Closed within due date" column to show: Gray when the case has not been closed yet (and therefore Closed date is blank) Green when Closed date is equal or less than Due date Red when Closed date is higher than Due date. I cant figure out the par for…