Formulas and Functions

Archives for March 2021

Does the formula reference not recognize column names at a certain length or have issues with '-' ?
I have some column names that are at or close to the max character limits and I've noticed that if I try typing in the formula with brackets, the references aren't recognized. I then would have to select the cell for each reference. Another work around I've found is to copy a valid formula and just replace the reference.…
Formula to calculate the Thursday after a specific date
Hi I have a cell containing the date at which some engineering tasks are completed (Complete date). I need to display in a separate cell the release date which is the new Thursday following the Complete date (or the Complete date if the Complete date is a Thursday) In a previous thread I found the following formula which…
Returning Indexed Values of Each Row for Latest Date
Hello, I am having trouble trying to return Current Value for each row corresponding to each name and for the latest date. Attached is a simplified table: Instead of manually inputting the 'Current Value', I would like to create a formula to return the latest cumulative value for each name row. I have tried using INDEX…
IF AND
Having trouble with a formula for RYG status. I need a statement for criteria: if its greater than 60% but less than 100%, turn the ball to Yellow. I have this: =IF(AND([% Complete]3 > 0.6, [% Complete]3 < 1, "Yellow")) But I keep getting either Incorrect Argument Set or Invalid Data Type. Why??!
If between certain numbers tier 1,2,3
I am trying to rank a set of projects by tier 1-3 based on a scorecard. If scores come in between 0-11, tier 3, 12-19, tier 2 and above 20 tier 1. I am trying to use an IF(OR type of formula and keep getting unparseable. My current formula is: =IF([Adjusted Score]2 = <11, "Tier 3", IF(OR([Adjusted Score]2 = >12, [Adjusted…
Mix icons and formula to connect "state" and "health"
Hi everyone, I created a "new health" column which is a drop down menu of icons (in the picture). These icons are mixed from two different sets of available icon. I want to create a formula to connect this "new health" and "State". For example, if state is "In progress/ On Track" it will show the green checkmark symbol. I…
Numeric and text formula
I would like to use a numeric and text dropdown but calculate the numeric in another column. Below is the best I can come up with (doesn't work) =VALUE(Impact@row(1 No Significant Impact))*VALUE(Likelihood@row(1 Extremely Rare)) Impact 1 No Significant Impact 3 Limited Negative Impact 4 Major Negative Impact 6 Serious…
Networkdays Formula
Help! I've tried a 1000 different versions and nothing has worked quite right yet! We are counting workdays between a due date (date- due) and date of issue correction (date- corrected). Also, I don't want to receive the #invaliddata if my date- corrected cell is blank. I'm good with: =IFERROR(NETWORKDAYS([Date- Due]@row,…
SUMIFS Formula not recognising a comma
Hi I wonder if anyone is having this problem or if this is user error, When creating a SUMIFS formula the first part apprears fine with the range and criteria range followed by criteria. However, when i enter the comma for the second criteria range and criteria I get an error. the following is my my formula which I think…
Formula for RYG for Project Schedule Status
We were able to create a formula for Green and Red status but have not figured out how we can automatically set the project status to YELLOW. Normally if an activity(s) are slipping that affects the critical path the project should be in a YELLOW state so a return to green plan can be worked on. So what I'm looking for is…

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Upcoming Events

Brandfolder Collaborator User Guide
Get ready to seamlessly upload and organize content within your Brandfolder! This on-demand session is designed to help you become a confident contributor and collaborator, ensuring your team has the right assets at the right time. What You’ll Learn: Maximize Features in the Asset Modal: Learn to navigate the asset modal,…
Brandfolder Admin User Training
Managing digital assets doesn’t have to be complicated. Join us for this two-part, on-demand webinar series designed to give Brandfolder administrators the confidence and skills to create smooth, efficient workflows. Whether you’re just getting started or ready to optimize your setup, these sessions will help you unlock…
Smartsheet Customer Success Onboarding (Japan)
Smartsheet カスタマーサクセスオンボーディングは、Smartsheet についての基本を学びたい方向けに毎週開催しているインストラクター主導のトレーニングコースです。ウェビナーは、始めるための基本、共同作業、およびさまざまな方法での作業の視覚化の基本など、Smartsheet のご利用を開始するために必要なすべてを網羅しています。

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions

Formula shows UNPARSABLE
I am trying to create a formula, and I keep ending up with unparsable. The name of the field I am accessing is called "Deliverable Number". Now For example, this field contains 2 characters I want to REMOVE Original: DEL #10 - TO Portal Strategy/Solution Desired: DEL 10 TO Portal Strategy/Solution As you can see the "#"…
Logic for If this AND if that then....
Does Smartsheet have the logic capability for me to ask it "If Column location is East, AND Column Credit request is travel credit" then use the standard default amount of $500 in travel credit amount column? Basically, I want to add logic where the answer displayed is based on 2 columns and not just one.
Using SumIfs with a date qualification
Hi Everyone. I would appreciate any feedback on what I'm doing wrong. In the formula below, when I hardcode my dates below, I get the correct answer back. =SUMIFS({Tech Enablement-NCAL - Allocation}, {Tech Enablement-NCAL - Timeline Range 1}, HAS(@cell, $Resource@row), {Tech Enablement-NCAL - StartDate}, <=DATE(2026, 4,…

Formulas and Functions

Discussion List

Help Article Resources