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
Formula / Multiple Sheet Reference Assistance
Hello! I have a summary sheet that references five sheets. I already had to modify my ranges for VLOOKUP on those sheets because I hit that 25,000 cell reference within moments. Three weeks later after changing all of my sheets, I'm ready to proceed to the next step in my project. I have one column that uses a VLOOKUP to…
Billing Date Formula
Looking for a formula that generates a renewal date. Here are my three column headers Column 7= Return Date Column 8 = Paid Through Column 9 = Next Invoice Date I want the Next Invoice Date to be either the Return Date or the day after the Paid Through Date. If the Return Date is Blank, the Next Invoice Date is the Paid…
Formula for Automatic Date Entry
I have 2 columns to be used in this formula: One is for the Date Completed in Date Format, and the other is the task Completed checkbox column. I would like to create a formula that automatically enters the date as Today's Date in the Date Completed column if it is empty, and the Checkbox is checked. I've tried to do this,…
Countif and countifs not updating
I have created a smartsheet with a number of different formulas in to pull together monthly figures. I then use this information to populate charts in my sights. One view I use is to count the number of new jobs added each month. This was working great until latest update. When I look at my sight the chart is no longer…
Countifs - with Hierarchy (Grandchild)
I am trying to write a formula to count the lowest level of "Completed" items. I have multiple Projects and different Status all on the same sheet. =COUNTIFS((CHILDREN[Variable Features/Tasks]:[Variable Features/Tasks]), Status:Status, "Complete", Project:Project, "Test") I'm not working in excel - I just wrote up the…
if and formula help required
Hi there, i have the following formula that i cant get to work. basically, i am raising a flag if a job is at one of multiple defined stages and withing a specific timeframe. its the multiple stages bit that i am stuck with. current formula as follows =IF(AND($[Cust Requested Date]$1 + $[Column54]$9 > [Cust Requested…
Count Ifs Formulas
I have created formulas to count the number of incoming quotes by month and according to if they have been Won/Lost/Pending. I use the exact same formula for March (month 3) and it works beautifully. For some reason the formula is not pulling in the info we have loaded up so far in April: =COUNTIFS([Conversion…
Adding hours and minutes
Hello All, I am new to SS and am trying to figure out how to add these hours and minutes for the year (there will be more added each week). I have found some posts saying this is not possible but they are from last year. Has anyone mastered this issue? [**Malformed image removed by moderator**]
Yet another RYG Balls Question
Hi all, Can someone either let me know or point in the direction of a post that would assist me in solving for the following. I am trying to create a formula that would: * Return red ball if date in a column titled "Last contacted" contains a date that is 30 days or over. * Return a yellow ball if the same column has a…
Formula Help
I have one column with a person's first name, then I have another column that I want to be just the first initial of the previous column's first person's name. Instead of typing in the first initial every time, I want to know if there is a formula I can put in the initial column??
Help Article Resources
Trending in Formulas and Functions
Identifying data associated with a previous row
Thanks in advance! From a listing of all meetings, I am hoping to pull in the date of the previous meeting. Note, there are no successors/predecessors with this (I've been able to successfully use Paul's post in other areas to pull in the task name and hoping somehow this could be similar?) Has anyone done anything…
Need Help with a Formula to Help Generate Chart Widget in Dashboard
Hey Folks, I'm trying to create a Chart Widget to show Metrics for how many lines are down due to specific inoperable Status type and thought I had this worked out but am struggling to generate that formula to do just that. So in essence, pull in a Count that references a specific building for how many of any Status type…
Auto populate a sheet based on criteria
Hello, I have a sheet that lists of all company projects with Project Names and many columns of associated date. I would like to have a sheet that automatically populates all of the project names based on a criteria. For example project not run in the U.S. One I have the project names in the the new sheet, I can use Data…