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.

How to Match Regions to States in a Column
I am creating a sheet that needs to identify the "region" based on the "state" in the preceding column. I've tried multiple formulas and get back an error message (unparseable). What am I missing? =IF([State]1=ny,nj,ct, "northeast",IF([State]1=nc,sc,ga, "southeast", IF([State]1=ca, az, nv, "west")))
VLOOKUP/IF or INDEX/MATCH?
Hello, I'm not sure which combination of formulas I require (VLOOKUP, IF, INDEX, MATCH...) In sheet 1 I have the columns "resource name" (text) and "onboarding paid?" (check box) In sheet 2 I have the columns "resource name" (text), "onboard?" (check box) and "milestone paid?" (check box) If the resource name matches, AND…
Checkbox Column + % Complete Column Formula
Smartsheet I am trying to use this Formula to calculate % Complete based on Checkbox, however the Checkbox is a different Column Called Done, not under the % Column. Can I do that? Existing Formula: =ROUND(IF(COUNTIF(DESCENDANTS(), 1) / COUNT(DESCENDANTS()) > 0, COUNTIF(DESCENDANTS(), 1) / COUNT(DESCENDANTS()), 0) * 100,…
Sheet Summary - SUM Chilldren Rows based on Status and Dates
Good Afternoon! I am trying to sum children rows where certain status' exist where the Pick-Up Date is today, within the next two days, and in the future beyond that. (Really, 3 formulas). I tried applying the following formula before involving dates to make sure the first part of logic is correct but it is giving me an…
Display Value IF Between Two Dates
Hello, I'm trying to create a formula that will display a specific value if a date falls within a certain range. Any assistance would be greatly appreciated. If “Target Update or Replacement Date” is between 10/01/20 and 09/30/21, then "Budget in Fiscal Year" = “FY 20-21”. If “Target Update or Replacement Date” is between…
#INVALID REF for Widget
Hi Community. I'm hoping someone can help me out. I created a task tracking sheet. A general rule is that our reporting month is always two month behind today. In my sheet summary I have a formula using index match, which works perfectly to my expected results. I am using a date reference table so I can see the month name…
SUMIFS Formula
Hi, I am trying to sum the numbers in the first column where the states are CA, CO, DC and IA. Could you please help. I a still learning formulas in Smartsheet. Thanks!
Date Range Formula
Hi, I'm trying to set up a formula to count the number of requests that were submitted between a specific date range. Below is the formula that I've written, however I'm still receiving an invalid operation error. Can anyone help me with this? =COUNTIFS({Citi Table Change Tracker Date Submitted} >= DATE(2019, 1, 1), {Citi…
if then... do this query.
In the below - column Principal Check I have put: =if([Principal survey drawing required?]2="Yes",[Principal drawing validated?]="Awaiting response","Not Required") I am wanting the Principal drawing validated? column to have an entry, "Awaiting response" based on Yes in column 'Principal survey drawing required?' column.…
Cross Reference Count Formula with Multiple Criteria
Smartsheet Community: I am trying to Count If the Checkbox is Checked, the Date is Less than August 5 or Blank. I know I am close but can not see finish the Formula Correctly. Current Formula Below: =COUNTIFS({Issue / Defect UAT LOG Range 3}, "1", {Issue / Defect UAT LOG Range 4}, <DATE(2020, 8, 5), {Issue / Defect UAT LOG…

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