Formulas and Functions

Archives for January 2022

Combining IF and COUNTM?
Hello, all! I need to count items in a multiple option column, but only within a certain date range. If this were a single selection, I would use COUNTIFS, but this is different. Do you have advice on how to do this? I appreciate any guidance you can offer!
Increment number if same date
Hello. I am using a formula to remove the slashes from a date. I would like to use the resulting value as the row's unique ID, by adding an auto incrementing 2-digit suffix. Here is the formula: =YEAR([Date]123) + "" + RIGHT(100 + (MONTH([Date]123)), 2) + "" + RIGHT(100 + (DAY([Date]123)), 2) + "-01" Which returns…
If there's an attachment, fill cell like this. If no attachment, fill cell like this.
I'm trying to put an automation in my smartsheet to fill in a column stating whether something is attached to that row or not. The row is completed by a form. I can get it to fill in the cell if there IS an attachment, but I can't figure out how to tell it what to fill in if there is NOT an attachment. Seems pretty simple,…
IF formula
I have 2 different sheets with same columns but for different projects. I used this formula for one sheet, but it does not work on the other sheet. Any reason why this is happening? I just need to count down the days remaining for registration. It has worked on all of my sheets in the past, but I can't figure out why it…
Summed values no longer having commas
Starting this week, I noticed all my summed pricing values in reports no longer have commas - has anyone else experienced this and is there a fix?
Today formula
I am trying to determine by looking at today's date and subtracting birthdate, how to get a person's age. I am new to this so any help is appreciated.
What are the 'Order' options for RANKEQ and RANKAVG?
Hey, I could likely come up with this if I sat down and experimented with the formulas for a while, but I really feel like they should be listed somewhere and better documented and I'm really hoping someone else has already done the legwork. I don't even know if 'order' serves the same purpose in both formulas. RANKAVG…
Nest IF formula
I have tried looking at the help but it doesn't provide details on creating nested IF formula. I am trying to say: If the column has name 1, then return statement A; if the column has name 2 then return statement B, if neither then return statement C. Any tips?
Help with Join(Collect to identify dependencies
This discussion was created from comments split from: JOIN(COLLECT 2 CONTAINS criteria with multiple returns Cross Sheet.
Report Summary why can't I format Currency?
I'm using reports to do "summaries" using Average and Sum. All underlying sheet columns are formatted in US Currency. The Total is displaying the number with the $ sign but no comma. Is there something I'm doing wrong?

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions

How to add varying months to varying dates?
I am really having trouble getting a formula to work here. I've gone through a number of support articles, but I'm just missing something. I want a formula that looks at Date Contract Executed and it adds Months Until Expiration to produce a new Date of Expiration. I've got my date columns as dates and the middle column as…
How Can I Calculate the Number of Hours between Two Different Time Formula Values?
I have a form that collects times of day, into a sheet via a dropdown List of times (9:00 AM, 9:30 AM, 10:00 AM, ect). I have converted these values into a time using the TIME function. So now I have 4 columns: Start Time (dropdown list value from the form) End Time (dropdown list value from the form) Start Time 12 Hr…
Automatic Phase from WorkBreakdown
Hello all! I'm modifying the Simple Portfolio Work Breakdown Schedule and having a little trouble. I like the way the Work Breakdown structure works but I'd really like an automatic way to track which Phase something is in. I know I could just add a column and manually select which Phase tasks and sub tasks are in, but I'm…

Formulas and Functions

Discussion List

Help Article Resources