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
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??
How to make formulas work on top of a sheet if new entries are coming on the bottom of the sheet via
Hi All I am really stuck with this as I need to amend my fomulas manually every time I have a new entry. Whenever there is a new entry ( via Web-form) I need to change the formula to include the new row instead of helping me with automation it has created extra work for me. Did any of you faced a similar problem? What…
Help Article Resources
Trending in Formulas and Functions
Need a formula for % complete for all phases on the parent project summary row. Row is non editable
I need an average formula in the parent project summary (row 23). Formula should include all phases (initiating, planning, executing, closing). The parent row is un-editable and not allowing me to enter a formula.
Formula for return value from a seperate sheet column if another column is one of 3 values
Hello, I am trying to create a formula that returns the value of a lets call it Column A in a seperate sheet if Column B in that same seperate sheet is any of 3 different words. Here is the formula as I currently have it. =JOIN(COLLECT({Scopes Range 3}, {Scopes Range 2}, OR(@cell = "In Progress - On Time", @cell = "In…
If Formula help
Hello, I am trying to write an IF formula for my smartsheet that adds the letter A, B, C or D or E to a column pending on a dollar amount listed in another column, but I cannot seem to get the ranges to work right and everything is listed as either A or B. Current base formula: =IF([Total Assets]@row >= 1000000, "A",…