Formulas and Functions

Formulas and Functions

Let the community help you with that tricky formula or function! See how others are utilizing the power of formulas and functions to save them time and create more powerful solutions with Smartsheet.

Discussion List

  • Pam Bussing
    Accepted Answer Pam Bussing 12 views 4 comments Most recent by Andrée Starå
    First post 12/17/19,Last post 12/17/19
    Hello, I have an "End" date column and I can calculate out 12 months using: =[End]@row + 365 However, I would like to have this date populate the cell only if the "%Complete" cell, in the same row, is 100%. In other wor…
    Formulas and Functions124
    Pam Bussing
  • Emily Alpert
    Answered - Pending Review Emily Alpert 55 views 1 comment Most recent by Andrée Starå
    First post 12/17/19,Last post 12/17/19
    Hello, I'm trying to count the number of projects delivered in the past seven days. This is what I have so far: =COUNTIFS(Status:Status; "Delivered";Delivery Date:Delivery Date; >=TODAY(-7)) I'm not sure what's going wrong or…
    Formulas and Functions551
    Emily Alpert
  • Brian Erdelyi
    Accepted Answer Brian Erdelyi 22 views 3 comments Most recent by Brian Erdelyi
    First post 12/17/19,Last post 12/17/19
    I am using the following formula to change a date into a billing quarter. =MONTH([Year-1/Deliverable-1 Invoice Date]@row) / 3 It works well except for any date in January. That returns a value of .3 (1/3=.3). The system automatically roun…
    Formulas and Functions223
    Brian Erdelyi
  • GeoSmile :-)
    Accepted Answer GeoSmile :-) 420 views 8 comments Most recent by Andrée Starå
    First post 07/19/19,Last post 12/17/19
    Hello all,  Now that formulas can be used in the contact column - fantastic!  - what is the syntax to reference a particular contact value? - I am trying to set up an IF <columnA value> ="blah" then assign contact X. I ha…
    Formulas and Functions4208
    GeoSmile :-)
  • Ramsay Zaki
    Sum all RowsQuestion
    Answered - Pending Review Ramsay Zaki 275 views 3 comments Most recent by Jim Schaffhausen
    First post 11/04/19,Last post 12/17/19
    My sheet has 3 columns (Team, Activity, Amount). I have a summary section at the top of my sheet where I want to use a SUMIF function to total up the amount for each Team. Generally, I can get the SUMIF function to work just fine. But, …
    Formulas and Functions2753
    Ramsay Zaki
  • Silvia Rangel
    Accepted Answer Silvia Rangel 21 views 7 comments Most recent by Andrée Starå
    First post 12/17/19,Last post 12/17/19
    Hello, First time posting here ☺ Trying to setup a flag for when the same data is entered in a row, but it's giving me a hard time ☺ help? (Image)
    Formulas and Functions217
    Silvia Rangel
  • Técnica Engenharia
    Answered - Pending Review Técnica Engenharia 21 views 8 comments Most recent by Andrée Starå
    First post 12/16/19,Last post 12/17/19
    I want to check by dates if you need to call the customer by activating the red flag, this red flag will serve as a pending call to a customer CRM spreadsheet. It is working correctly, it happens that when the date cell is empty the flag …
    Formulas and Functions218
    Técnica Engenharia
  • Natalia
    Answered - Pending Review Natalia 43 views 8 comments Most recent by Paul Newcome
    First post 12/16/19,Last post 12/17/19
    I am attempting to write a formula based on 2 separate criteria, though I feel like I need some type of "BUT" statement and could use some help. I want a picklist column to read YES or NO depending on the following: IF Ins Status…
    Formulas and Functions438
    Natalia
  • Jamila
    Accepted Answer Jamila 269 views 2 comments Most recent by Jamila
    First post 12/16/19,Last post 12/17/19
    I am trying to develop a formula to flag tasks that are less than 50% complete but more than 50% of the duration has passed. I'm not sure if my math is correct. But aside from that, can Smartsheet perform functions like the one I am propos…
    Formulas and Functions2692
    Jamila
  • Rob Pritchard
    Answered - Pending Review Rob Pritchard 56 views 11 comments Most recent by Andrée Starå
    First post 12/14/19,Last post 12/17/19
    We have a master job list which we use to create our new project references. There are a lot of requests with a range of values ? I have a separate sheet where we populate with those projects we want to keep a closer eye on costs on picked…
    Formulas and Functions5611
    Rob Pritchard
  • TRach
    Answered - Pending Review TRach 135 views 2 comments Most recent by Paul Newcome
    First post 12/05/19,Last post 12/17/19
    I am trying to change the drop down value on my Health column based on the current date. Is this possible? ie. If the start date value is in the past, the health column changes to "At Risk" which is one of the drop down choices.
    Formulas and Functions1352
    TRach
  • Netanel yosef
    Answered - Pending Review Netanel yosef 276 views 7 comments Most recent by Paul Newcome
    First post 12/15/19,Last post 12/17/19
    hi, i have task in smartsheet and each task i give factor which amounts 100%. and each task i give actual % complate. the problam is when i try to calculate the Weighted average i need to multiply each task in his factor. if i delete or ad…
    Formulas and Functions2767
    Netanel yosef
  • Técnica Engenharia
    Answered - Pending Review Técnica Engenharia 21 views 1 comment Most recent by Andrée Starå
    First post 12/17/19,Last post 12/17/19
    I would like to make a formula for calculating percentages based on the progress of the task as we contract projects. When you set the option "completed" the formula passes the value of 100%. When "in progress" the fo…
    Formulas and Functions211
    Técnica Engenharia
  • Stavros_McGillicuddy
    Answered - Pending Review Stavros_McGillicuddy 126 views 2 comments Most recent by Stavros_McGillicuddy
    First post 12/16/19,Last post 12/17/19
    I have a [RowID] column that is populated by this formula =IF([email protected] = 0, COUNT($ANCESTORS$1:[email protected], "")) It returns sequential numbers but, I need it to only count if Ancestor = 0 Secondly, I need the result fill …
    Formulas and Functions1262
    Stavros_McGillicuddy
  • James Emge
    Answered - Pending Review James Emge 34 views 12 comments Most recent by Andrée Starå
    First post 12/14/19,Last post 12/15/19
    I am working on a display for RYG indicators where my data collection sheet will look at today's date, a site that has either confirmed a check, failed one or has not performed it yet. I have pieced this together from some of the terrific …
    Formulas and Functions3412
    James Emge
  • Brandon Schwartz
    Brandon Schwartz 2.2K views 3 comments Most recent by Gwyneth C
    First post 06/08/16,Last post 12/15/19
    Hello,   I am trying to create a nested IF formula based on the criteria below. Below is also a screenshot of what I am trying to do:     =IF([% Complete]1=0,"Not started" =IF([% Complete]1>=0 and <=1, "On trac…
    Formulas and Functions21653
    Brandon Schwartz
  • Michael W.
    Michael W. 285 views 4 comments Most recent by Andrée Starå
    First post 11/13/19,Last post 12/12/19
    Greetings, In a Contact Column, and in a ancestor/child relationship, I would like for parent rows to summarize/list all persons in the child tasks.  Is Smartsheet able to perform this action? E.g. If the child records list: * Jo…
    Formulas and Functions2854
    Michael W.
  • Sldollman
    Sldollman 55 views 6 comments Most recent by Andrée Starå
    First post 11/12/19,Last post 12/08/19
    Hi community, I'm attempting to find a formula that will return the parent row where the end date is equal to the current week.  I have created a helper column that calculates the current week number.  Now the question is what to do with…
    Formulas and Functions556
    Sldollman
  • Chris Trumpey
    Chris Trumpey 83 views 1 comment Most recent by Genevieve P
    First post 12/06/19,Last post 12/08/19
    Hi All, I need some CountIFS help with counting dates, I have a sheet that has a column called Device that has either New or Existing. Then my second column is Date Installed. If the Device is New and the Date Installed has a date then I …
    Formulas and Functions831
    Chris Trumpey
  • Jon Stikka
    First post 12/02/19,Last post 12/08/19
    I would like to set up Sheet Summary to show a counts of Open Tasks, and Completed Tasks - based on the column "Done" being checked or not.  The "Parent rows" are for reference only, they are not Tasks and shouldn't be…
    Formulas and Functions1874
    Jon Stikka
  • mel_berk
    mel_berk 129 views 3 comments Most recent by Andrée Starå
    First post 12/03/19,Last post 12/08/19
    How do I format my At Risk column so the flags only appear in the "children" rows, as shown in the screenshot? (Image)
    Formulas and Functions1293
    mel_berk
  • lisarae723
    lisarae723 83 views 6 comments Most recent by Andrée Starå
    First post 12/04/19,Last post 12/08/19
    Hi everyone! Im having issues with a status column. My goal is to update the status if a job has hit 10 days or more and has not been approved yet. I was going to trigger it by using the (system column) "Last Modified"  Here's …
    Formulas and Functions836
    lisarae723
  • lesmickin
    lesmickin 61 views 1 comment Most recent by ron.judenberg112096
    First post 12/05/19,Last post 12/07/19
    I have a column that has approx 20 multi-select options.  I am wanting to run a report that shows a count of any instance in the sheet where let's say HSW has been selected.   Column is annotated as a multi-select column/field.  Column na…
    Formulas and Functions611
    lesmickin
  • Paul Newcome
    Paul Newcome 32 views 6 comments Most recent by Andrée Starå
    First post 12/06/19,Last post 12/07/19
    Has anyone been able to figure out how to do a find/replace on a checkbox type column?   I have tried 1, true, TRUE, True, "1", "true", "TRUE", "True", and none of them seem to trigger a match.
    Formulas and Functions326
    Paul Newcome
  • janger
    janger 25 views 1 comment Most recent by Andrée Starå
    First post 12/06/19,Last post 12/07/19
    Is there a way to count the number of rows of text within a cell?  I know this will change dependent on column width but if I keep my column width static, I need to know how many rows of text I have within a cell.
    Formulas and Functions251
    janger
  • kolfinna
    kolfinna 47 views 3 comments Most recent by kolfinna
    First post 11/19/19,Last post 12/06/19
    Hi everyone, Fairly newish user when it comes to formulas here; please forgive me if this has been answered elsewhere and I missed it in my searching. I'm wanting to assign a value to a column named Bucket based on the value in the Start…
    Formulas and Functions473
    kolfinna
  • kane.godfrey
    kane.godfrey 31 views 3 comments Most recent by kane.godfrey
    First post 12/05/19,Last post 12/06/19
    Hi,  I need help to solve this puzzle. I'm using this sumif formula   =SUMIF({Supplier Invoice register Order Number}, [PO Main Contract]10, {Supplier Invoice register value}) to sum up the total value of invoices against a particular o…
    Formulas and Functions313
    kane.godfrey
  • slilienfeld113476
    slilienfeld113476 140 views 1 comment Most recent by Morgan Marquez
    First post 12/05/19,Last post 12/06/19
    I'm attempting to create a formula that will count the number of cells in a column from range in another sheet, that only includes the cells with a checked box.   In other words, there are two columns to include in the count from my Comm…
    Formulas and Functions1401
    slilienfeld113476
  • GrahamR
    GrahamR 22 views 5 comments Most recent by [email protected]
    First post 12/05/19,Last post 12/06/19
    Hi,   I'm setting up a page where you enter 5S audit test data from a Form. It puts a new row in every time with the 5 different 5S scores each in it's own column. We need to find a way to pull the data from the most recent new row. I n…
    Formulas and Functions225
    GrahamR
  • Michael Gerdes
    Michael Gerdes 32 views 2 comments Most recent by Richard Rymill SBP
    First post 12/05/19,Last post 12/06/19
    Hi, I have an inventory sheet where users are putting products in a box, the user would like to minimize the number of clicks as they are moving large number of products a day but these products will go into several boxes. is there a way t…
    Formulas and Functions322
    Michael Gerdes