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.

Return a sum using using sumifs and dates
My objective is to return a sum of all 'Work Hours' that are in a department (Wire in this example), that fall within a date range of the 'END' date that Smartsheet is reporting.. This formula returns a value, but I've checked manually, and I've determined the value is incorrect. I've exported a filtered view of the sheet,…
COUNTIFS Issue
Hi, I am trying to count between a range of numbers. The problem is smartsheet seems to only count the first number and everything after that. It doesnt seem to count between the range. Range to count is Probability - This has numbers ranging between 1 and 0.50 I have tried the following =COUNTIFS({Master Programme Range…
Nested IF Statement Help Needed
Hi there, Here is what I am trying to achieve: If the % Complete column =100%, then return Gray to the status If a task is overdue (using the End Date column), return Red to the status If a task is due within 5 days, return Yellow to the status Is a task is due in 6 or more days, return Green to the status If there is no…
today() automatically change or not
Hi, confused for using Today() formula in my sheet.. Currently using countif and sumif formula based on today() date. please confirm that formula automatically changed when date today() change or when I can manually activate my sheet first and then formula changed.
Project look ahead
Hi, I am looking to add a widget to a dashboard to show a two week look ahead of a project. I have a project sheet and essentially want to create a formula to check a box, from which I can use to trigger a report.
How to uncheck a row field if another field in the row is set to a specific value
What I have so far: The box gets checked (true, 1) when a target due date is within 60 days of today. My current formula is: =IF([Target Due Date]3 - 60 < TODAY(), 1) What I hope to achieve: Find a way to uncheck (false, 0) the same box when Status is set to Closed. I just can't seem to come up with anything that works for…
IF AND with Date
Hello, I am trying to achieve the following: If the date is grater than 2020,6,30 and the salespersonA is "D" and salespersonB is "T" then calculate 30% of net profit, otherwise leave the cell blank. What I have so far is below and I have an error "#unparseabl" =IF(AND(Date2676 > 2020, 6, 30, [SalespersonA]2676 =…
Count # of Issues by Date
I have a issue tracker where issues are submitted daily. Looking for a way to count # of issues by Date (# Opened vs # Closed), using the created and modified system columns. Any help would be greatly appreciated!
How can I update this formula to exclude a column?
=COUNTIFS({Territory}, HAS(@cell, $Territory2), {Domain Owner}, HAS(@cell, $[Tech Domain]2), {Status}, HAS(@cell, "Not Started")) - COUNTIFS({Territory}, HAS(@cell, $Territory2), {Domain Owner}, HAS(@cell, $[Tech Domain]2), {Status}, HAS(@cell, "Not Started"), {Post TSA}, 1). I need to add to the formula (I am not good at…
Bringing back values from another sheet
Hi all, Is is possible to bring back all the values from a column in a different smartsheet to a sheet I'm working on without "looking up a value" like you would i.e. using a vlookup formula? Thanks, Carlos

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

How to update certain cells in column based on one cell's input and other column's same ID #?
I have a very simple test script testing tracker located here: I want to be able to do one of the following: 1. Update the Status column for all testers with the same test ID to "Duplicate" once one person tests and puts "FAIL" in the PASS/FAIL column. 2. Do not copy defects to defect tracker sheet if one of the rows with…
Duplicate Formula
I have a Smartsheet which is fed from a form an Intake team is using to gather prospect information and we are having issues with duplicate entries. The sheet/form has a field for prospect first name and another for prospect last name which we would like to check for duplicate entries of. I created a column combining both…
Countifs date is this month and a day of the month
Hello all, I am using the formula to count from a list of dates in another sheet to find all entries that are from this month. =COUNTIFS({Date}, IFERROR(MONTH(@cell ), 0) = MONTH(TODAY()), {Bay}, Bay@row) I would like it to also register if it is the 1st of the month (for the column named 1st) and the 2nd of the month (for…

Formulas and Functions

Discussion List

Help Article Resources

Categories

Popular Tags