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.

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

Counting the Number of times certain text appears in a Column
Hello! I need assistance to create Sheet Summary items to count all of the instances where certain Unit Code(s) is listed in a column. Some codes can be combined with others (C1 + C2) vs by themselves (C2). So, to start I need to count the number of times Unit Code "C2" is listed/mentioned. Eventually I will also need to…
Cross sheet reference strategy
Hi Folks, We have two sheets, which are used by two stakeholder groups. I've populated sheet B with some of the data from sheet A (the main sheet). To do this, there is a unique and corresponding ref on both sheets, and this has been used for a vlookup formula. We've now hit the max cross references and I've converted the…
I am attempting to merge/combine notes either from one sheet or from two different sheets.
Sheet 1 contains a column for "previous" notes that are drawn from sheet 2 and column for "current" notes that are entered manually each week. Sheet 3 is an archive of all weeks prior. This archive can have multiple "matches" in the primary column. If the primary column information matches between sheets, I would like to…
Number of days it took a stock to rise/sink above a certain level.
I tried to use multiple IF formulas, but it got too complicated for me and I also couldn't get them to work. I am using Excel 2019, 64-bit. I'm not positive that my explanation fully explains what I am trying to do, so it's probably better to look in file 1. 1. In 'FILE1', find if stock in A1 is found in column A in…
How to Calculate number of months from Today to an End Date
Is there any advice on how to calculate the no. of months between Today and an End Date (mostly likely not in the same year)?
Using formulas to automate a date
Hello! I have a column that identifies a specific week, e.g., week 1, week 2, etc. I would like to create another column that automates a specific date based on week entry. For instance if Week 1 is entered, the date 1/25/21 is automatically entered in the date column. Thank you for your help!
SUMIFS one of two names found
If Team One OR Team Two are found in the 'Team' column, add up the values found in 'Story Points' column. This formula gives me an error: =IFERROR(SUMIFS([Story Points]:[Story Points], OR(Team:Team = "Team One", Team:Team = "Team Two"), [RezW#]:[RezW#], [Week#]@row), "nope") The formula below works fine for a single team…
Convert excel date formula to Smartsheet
How do I recreate this formula in Smartsheet?
COUNTIFS with an OR formula
I need to add up 3 different regions as well as 1 location within that region to see what deals have been closed in 2020. I am using this formula and it's coming back unparseable. It's due to the multiple regions I'm trying to pull in - I think. =COUNTIFS([Business Unit]:[Business Unit], OR(@cell = "Region 1", @cell =…
Formula with TODAY()
=IF([Finish]3) < TODAY(), "Red", IF(AND([Finish]3 >= TODAY(), [Finish]3 < TODAY(+7)), "Yellow", "Green")) is giving me a UNPARSEABLE error. Any suggestions?

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Popular Tags

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions

Convert Quarters to Date
I have a list of dates that are Quarter/year (in a format like Q4 2031, Q1 2038, etc.) and would like to use a formula to show the last date in the quarter. So: Q1 2031 = 03/31/2031 Q2 2027 = 06/30/2027 Q3 2033 = 09/30/2033 Q4 2035 = 12/31/2035 I would appreciate help creating the appropriate statement!
% complete using a duration of dates
I am trying to get a percent complete using dates. For example, I have a start date and a finish date to get the duration (let's say that is 96 days) and I want to find out the percent complete using today's date (automatically updated each day) as the task progresses in time. I can't seem to figure out how to use TODAY()…
Countifs formula not returning a value.
Challenge: Countifs formula is not returning a value from a cross sheet. Any help would be so greatly appreciated. Summary: I am trying to pull in counts for a specific month and status for an application. The formula doesn't state any errors but it doesn't return the values. Formula: =COUNTIFS($Name18, {Status}, [JAN…

Formulas and Functions

Discussion List

Help Article Resources

Categories

Popular Tags