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
AvgIF(Children)
Is there a way to get an average of selected cells (all the Children of a column) only if they are not blank? I am stuck and trying to automate the functions as much as possible. Thanks a ton in advance!
SUMIFS using Fiscal Year Date Range
I'm trying to sum monetary amounts for a certain customer while referencing the data from a separate sheet. However, I only want to sum those amounts that fall within the FY20 (fiscal year) dates of 4/1/19 to 3/31/19. Here's the start of my formula that sums all dates, but what conditional formula do I need to add that…
Sum of Values in Another Sheet, Using Collect
I would like to find the sum of the enrollments in a particular course from one sheet (Sheet 1), and display in another sheet (Sheet 2). Sheet 1 There are three columns, Course, Students, Term. Course | Students | Term BUS 101 | 12 | Summer BUS 101 | 33 | Summer BUS 101 | 15 | Fall ACT 223 | 16 | Fall CAR 322 | 4 | Fall…
Nested IF Statement
Hello, I am trying to create a status of "Adheres" or "Past Due" based of off comparing two date fields (A Target Date and an Actual Date). When I use this formula I get what I need. =IF([Target Audit Start Date]8 > [Actual Audit Start Date]8, "Adheres", "Past Due") The issue is that the actual date field will not be…
Count Number of Open Items in the Last Month by Day
I am trying to make a chart showing the number of open items each day in the last month. In the last month will be flexible so this will be a item that autopopulates the chart as the month goes on. I have a column for the formula =TODAY(-1), =TODAY(-2), etc. to populate the dates for the last 30 days. I have a "received"…
HELP with IF formula and Circular Reference
Hello, Here's the set up: I have Four columns I'm working with: Progress (Drop down menu) [the main option i'm working with is "to do"] Duration (Duration) [This column is based on how long we have to complete the project] H# (Number) [This is just a column that takes duration out of days to just numbers] Allocation %…
Formula to auto populate a number based off cell input
I'm looking to see if there's a formula or other means to have Smartsheet auto populate a quote number and duplicate that number if the same name is added later on. I am working on building a quotes log where we input a project name in Job Name which then auto fills to the Job Name Master. Next to the Job Name Master…
Formula - IF and VALUE function
I'd like to figure out how to execute the following function. If a cell contains certain text, fill in a numerical value in another cell. For example: If cell A1 says "Request Type A" then fill in "3" in cell A2. If cell A1 says "Request Type B" then fill in "5" in cell A2.
Formula will function in Excel but not SmartSheet
For some reason I am having trouble getting this formula to function in SmartSheet (even though it works in Excel)... I am wanting to populate a "Y" in 'Valid Approval?' column only when one of the following 2 situations occur: 1) 'Primary Approval' says "Approved" AND 'Secondary Approval' says "Approved" 2) 'Primary…
Count Children in adjacent column
How do you write a formula to count certain values of an adjacent column from where you want to the count value displayed. In my sheet I need to count open positions and filled positions based on the status. My sheet is very large so I have to replicate this many times with varying row #s so looking for a quick way to tell…
Help Article Resources
Trending in Formulas and Functions
How do I get a cell to display the value of the last cell in a column excluding one of the values?
Hi All, I have a column in a project plan with a droplist of certain values. I want to display the value of the last cell in the column that has any value except Not Started. Would someone please provide a suggestion?
Replacing NULL in a column with zero
I need a formula(s) to use that can replace "NULLS" in a worksheet with zero or replace nulls with "Blank". One column contains text values and the other column number values A formula to replace "NULLS" with zero in a number value column Another formula to replace "NULLS" in a text value column with "Blank"
Form: Require "Need order by" field to be AFTER today.
We have a form that has a DATE field called "Need Order By" so that the user can tell us the date the the order must be fulfilled by. Can you guess what date users MOST OFTEN use? Yeah, you got it! a date in the PAST. We are very anxious to find a way to force the user to input a date that is in the FUTURE. Can anyone tell…
