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.

Excel Formula Error
Hi All, I've referenced the forums for help and thought someone might help me with the "unparseable" error I am getting. We are trying to do complex formulas: =COUNT(IF(FREQUENCY([Listing Key]2:[Listing Key]20)>0,1)))) The "[Listing Key]2:[Listing Key]20" reflect the chosen cells to crunch the formula. Attached is a…
Web form populates parent/child relationship through category
Hello, was hoping to get some help on an issue i am having .. we have an inventory control sheet .. we would like to use a web form(s) to not only take out something of the inventory but to also add something to the inventory. I added a Category column and that is reflected in a dropdown list that matches all of the…
Completed Tasks Past Due Date
I am trying to make a column with stars that is starred when a date is filled into the completion date column past the due date. The only problem with this is that we have a recurring maintenance sheet, and there is a formula for the Due Date column that automatically populates it based on the last completion date. So…
Formula Help please
i have a column titled # Wins and wound up with a percentage towards my Wins goal. That said i want to use RYG lights based on how close i am to the goal. So here's what i wrote but i know i'm missing something. Any help would be much appreciated. =IF([# Wins]5<=.66,"Red",IF(AND([# Wins]5>.66>.9,"Yellow,IF(AND[#…
Match Two Entries from Two Separate Form Sheets, Assign Status
I have an "Assignment" form that creates a system generated number. The crew foreman will use this form for their assignment, then they will populate a "Production" form using the system generated number from the "Assignment" form to reflect the production done for that assignment. I'd like have a RYG ball on the…
return list from another sheet
Anyone able to offer some help on the formula below - trying to return a list of information relative to an employee ID number to a column. There will be several matches per ID, e.g. Matt worked item 1.. 2... 3.. and 7. I want to return the information relative to that ID for those entries. Thanks in advance!…
Summing of colored "conditional" cells
Hello All, I am trying to count or sum how many colored cells there are within a row. The rows consist of test questions and are set to turn red if the condition is not met (answer is incorrect). Is there a way to sum all of the condition not met cells? There are 25 cells that have conditions in them.
Nested IF Then And Statment
I am trying to finalize a Nested If Then /And Statement. In one of my logical functions, I am trying move to the next condition if a cell has a date input into. When I try to use the logic if cell > 0, it returns an error. What would be the best method for moving the formula to the next logical statement?
cell reference based on date
We have a yearly schedule for all our field guys that is updated a week prior and sent out to the field so everyone knows where they will be working for the upcoming week. My question is can I tie a roll up sheet to the master schedule sheet that I would input a date on the roll up and based on that date grab the matching…
Help on formula: countifs with multiple criteria
Hi everyone, I need help on the below formula. I am trying to get the number of active projects based on the columns Section and Stage (see attached image). The 2 sections are called Food and Non-Food. The active stages are called Planning, Strategy Decision, Tender, Evaluation and Contract. Other terms applied in this…

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

Get a Countif on values in a multi-select column of cells
I am trying to write summary formulas to determine how many values appear of each in the drop-down of a column. I tried adding a wildcard, I have tried HAS, and tried CONTAIN, but I'm still not getting the right values. Currently I am getting 0 as a return value on this formula: =COUNTIF([ROI Type]:[ROI Type], HAS([ROI…
summaries in report
Hi i'm trying to build a report with summaries options. i have two column that i want a Min and Max summary. the column for the Min i a texte column and works well. but the other column has formulas and the Max doesn't works. there is a way to fix that? (instead of creating another text column in the source sheets?) here…
Converting email addresses with hyphenated First and/or Last Names
I am trying to convert email addresses into a Name field with the employee's First and Last Name, capitalizing the first character of each part of the name and keeping the hyphen in any part of the name that has one. I started with hyphenated first names so taking shmoe_joe-bob@company.com and converting it into Joe-Bob…

Formulas and Functions

Discussion List

Help Article Resources

Categories

Popular Tags