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

  • How to use All 4 Formulas together "IF AND NOT OR "
    In Formulas and Functions
    I have to create a formula that returns a value "1 or 0", so I use the IF statement. Now to satisfy multiple conditions, I am using the AND statement.  Also, to I want to check if the statement is not true, Hence I use the NOT f…
    11 5
    11 views 5 comments Most recent by Mike Wilday
  • substitute plan date with actual date
    In Formulas and Functions
    Hello, Trying to create a formula that can substitute the plan date with actual date. There are columns of Plan Start/End, Actual Start/End on the sheet, and the actual start & end are initially left blank, and once a task is kicked…
    11 2
    11 views 2 comments Most recent by Mike Wilday
  • Number of NetDays since last event
    In Formulas and Functions
    Hi, Looking to calculate the number of netdays since an event was recorded. I have a {Date of Incident} column and a {Classification} column   Looking for the number of days since a specific classification was reported i.e. {Classifica…
    164 6
    164 views 6 comments Most recent by Mike Wilday
  • IF and VLOOKUP with multiple options
    In Formulas and Functions
    Trying to summarize from a worksheet to another (summary worksheet).  The VLOOKUP should give an "X" if includes "August" and instead gives the same answer for every search since there are multiple of the original searc…
    2 views 2 comments Most recent by [email protected]
  • % complete in a row
    In Formulas and Functions
    Hi, i am looking for some help. I have a project which has 360 locations, resulting in 360 rows. i have 8 status columns in each row. is there a formula that will replicate a % by counting each status column that is complete and making th…
    11 2
    11 views 2 comments Most recent by dave.mcpherson56751
  • Matching a percentage dropdown entry
    In Formulas and Functions
    I've got a pretty simple requirement which I'm struggling with. I have a dropdown defined with % values for completion (ie 100%, 75%, 50% etc) and I want to create a formula which searches for completed tasks (ie any cell with 100%). I ima…
    22 3
    22 views 3 comments Most recent by Andrée Starå
  • Formula help
    In Formulas and Functions
    Hello,  I have been trying to work this one out.  Attached is a picture of the column that i wish to tally.  This column is a checkbox column and there are multiple parent lines (these are the colours lines) The top line is the grandparen…
    11 1
    11 views 1 comment Most recent by Andrée Starå
  • SUM more results from more IF conditions
    In Formulas and Functions
    Hi All, image different questions (1 for each column) with 3 possible drop-down answers (Yes, Maybe, Not). Every combination question/answer generate a different score. Ex: Question 1  * Y - 3 points * M - 5 points * N - 8 points I nee…
    1 6
    1 view 6 comments Most recent by Mike Wilday
  • If(AND) across a range
    In Formulas and Functions
    I have a hierarchy of checkboxes.  I am trying create a IF(AND) formula to at the Level 1 checkbox to be checked once all the Level 2 boxes are checked.  I am able to make it work when I reference each of the cells , for example:  =IF(AND(…
    102 2
    102 views 2 comments Most recent by Paul Newcome
  • Unparseable Error in IF(And formula
    In Formulas and Functions
    I'm trying to create an IF statement that relies on the data in 2 cells.  Basically, if one cell is checked off and the other is empty, I want to create a formula on a new cell (which I will hide) so I can ultimately send an update request…
    41 5
    41 views 5 comments Most recent by Paul Newcome