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
Gantt View Settings Start Date Column Selection
While in the settings for my Gantt View my "Start Date Column" drop down selections does not include the column I want to use. It was there before. What could have happened. It is formatted as a Date. Also baselines keeps being turned on even after I turn it off and remove it. How can I get that to stick so I don't have to…
Sheet Summary formula
Hi, I am using the below query to fetch a value from Finance column but this works if my criteria of Req Type = P but fails for Req Type = L. I believe this is something to do with INDEX Function but not sure how my requirement can be achieved. Can someone help me on fine tuning this query please! Query works for Req Type…
COUNTIFS FOR MULTIPLE CRITERIA INCLUDING CHECKBOX
Hi Need a COUNTIF (or COUNTIFS) formula that can count number of times a value appears in a column AND when checkbox appears on same row as the value. have previously counted using just the number of times a value appears but now need to add the checkbox element. Formula that has been working when a simple count:…
Column sum
Hi All, I have a sheet with many columns (150) with column formulas. I need a way to sum amounts for each column without disturbing the column formula Just as example, let's sell houses Each sale is input as a new row via a form with delivery dates and automatically allocates percentage for each contractor involvement…
COUNTIF of CHILDREN with HAS
I'm running into an issue with a formula that I'm trying to write that counts the results of a multi-dropdown column using the CHILDREN hierarchy. =COUNTIF(CHILDREN([Event Tier]165), HAS(@cell, "Tier 1: Off-Site Event")) I'm looking for any cells that contain the answer "Tier 1: Off-Site Event" for all the children of…
Countif the Year 2021 on a Column Returns #invalid data type or zero.
https://app.smartsheet.com/sheets/xXvWq9JrWMjRXGHv7jcV4Q4GF45wJWc4C32GcHw1
COUNTIFS if a Date cell is within 30 Days
Hi, Is it possible to COUNTIFS with one of the criteria being a date that is equal to or within 30 days to that set in another cell? In the past I have used a helper column to count the days and just count the cells that fell within the number of day <=30, <60, etc. Any help would be much appreciated. Thanks Richard
Formula to find latest/most current
Hey friends! I have two sheets. I need one to behave as a "log" (columns: Machine SN (Primary), Machine Hours, Machine Status (Dropdown List), Current Machine Location). This log will be populated by Forms. The second, separate sheet will be a "ledger" where each row will represent a different, individual machine (Machine…
If Not Blank Statement Help
Hello, I am trying to write a Column Function If statement in the "Task Status" column that will update my status to be "Complete" or "Incomplete" based on if certain cells on my smartsheet are blank or not. So far this is the formula I've put together, but I can't quite get it to work. Would somebody be able to take a…
Find Unique Dates from 2 Date Columns
Problem of the Day: I have 2 date columns with different dates. I want to create a 3rd column that lists only the unique dates from both columns. Suggestions?
Help Article Resources
Trending in Formulas and Functions
Need to find all instances of value and return any non-blank cell from another sheet?
Hello Everyone, I'm stumped and need some assistance. What I am trying to do is look for the value in the Cable Number column (Cable Number]@row) on the master sheet in the Cable Number Column on the Update Tracker (Update Tracker Cable}). There will be multiple times this value is in this column but some dates will be…
Hello I am trying to create a formula that returns a specific date when a dropdown selection is made
class requested start date Jan 13, 2025 - July 14, 2025 Oconomowoc, WI Jan 13, 2025 - July 11, 2025 Huntsville, AL (new site) Feb 10, 2025 - Aug 11, 2025 Amsterdam, NY Mar 24, 2025 - Sept 22, 2025 Fontana, CA Apr 21, 2025 - Oct 20, 2025 Oconomowoc, WI May 13, 2025 - Nov 17, 2025 Huntsville, AL Jun 16, 2025 - Dec 15, 2025…
How Do I add Sum(Children) at the bottom of the children rows
Hi, How do I use the =Sum(Children) function in Smartsheet to sum child rows and display the total at the bottom. I usually create a parent row, then add rows beneath it, indent them and add the formula =Sum(Children) to the parent row to calculate the total. However, that structure was confusing for users, and they…