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.

Formulas and Functions

Stumped by a formula? Ask for help or find examples to see how others use a formula to solve a similar problem.

Count number of contacts
Hi, I need to find how many times a specific team member appears in multiple contacts cell. I understand that Contains doesn't work on this kind of cell so I tried HAS but I don't get the expected results. =COUNTIFS(Lead:Lead, HAS(@cell, Team1)) Any idea? Thanks
Formula to make cell blank if Complete Date has been filled in
Hi There Hope you can help. This is my Spreadsheet - Once the Date Completed has been filled in, I don't want to see the Days Over Due number. Is there a formula for this? Would highly appreciate you help. Thank you
Cell link not updating consistently...?
Hello All, I am having trouble with getting a report to update consistently with cell links. This screenshot shows the cell history showing it only updated at 4.57pm today. This is the formula in the cell "=SUM(COLLECT({$_Today}, {Todays_Date}, Date@row))" where $_Today is an external reference to a column with the value…
How to get Countif with Discontinous Cells in a row?
=COUNTIF([Part # 1]@row, [Part # 3]@row, [Part # 5]@row, <>"")) Isn't working - results in error # Argument Trying to count each cell if not blank...
Text to Column replacement
Hi Everyone, I wanted to ask how can I extract a number from a column My column has this numbers HS7.36 MC14.26 I did try the left and right formula, but I need a way to exclude the letters. thank you
% Complete is not calculating properly
I have a Smartsheet Project Plan and the % Column is not adding up correctly. see
I want to use the below formula but instead of SUM IF I need to work out Average IF
=SUMIFS({Quality Checks Tracker FY21-22 Range 1},{Quality Checks Tracker FY21-22 Range 2}, [Primary Column]@row) So i want to work out the average %% if the first column says "full Inspections" ect ect I have the formula working with sumif on the first number column but now need to work out the average %%
Red/Green/Yellow Formula
I've tried a couple of things and I am at a loss. I have a Planned Start Date, Planned End Date, and a % Complete column. I would like the dots to be working as follows: Red - When the planned start date prior to today and % complete is not = 100% or 1 Yellow - When the planned start date = today and the % complete is not…
IF AND OR Formula Help
Hello! We are building a questionnaire that shows the results of a leadership style. The formula that we are trying is: =IF([Total Value - Even]@row >= 10, IF([Total Value - Odd]@row >= 10, "Coaching", IF([Total Value - Even]@row >= 10, IF([Total Value - Odd]@row <= 9, "Directing", IF([Total Value - Even]@row <= 9,…
Trouble with AVERAGEIF
Good day! Trying to show the completion average for an individual and thought this would work but I must be using it wrong. My reference column calculates the completion average per project, which have has some blanks, and I want the overall average of those per person. Here is what I tried, and failed. Am I on the right…

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Trending in Formulas and Functions

Formula Help: Do This Week, Do Next Week
Hi, gang! I need to create a formula that populates Do This Week, Do Next Week, Do Three Weeks for my boss. I have Start and Finish date columns as well as a dropdown Do column with those three as options. We're not looking for reports, but rather this summary view in Card View for him. Thank you for your help!
Giving an output based on text from multiple selection dropdown
Good Afternoon, I've been racking my brain trying to get this function to work. I have a column set up to allow multiple selections of specific text with the column name "Processes." I want the cell to find the specific text and give a number value. I'll then pull from a "Due Date" column giving the processes start date…
How to use IF and IFERROR?
I am writting the formula for counting the result of the work of my team. The result is counting from the complete date< expected date would be green and if it is greater, it would be red. However, in some cases, even i did not fill in the expected date and completed date, but the result is still showed as green. This…

Formulas and Functions

Help Article Resources

Categories

Popular Tags

Formulas and Functions

Discussion List

Help Article Resources

Categories

Popular Tags