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

  • Help on Formula: automate status based on dates
    In Formulas and Functions
    Hi all, I'm pretty new with formulas and I wanted to automate the status of each projects based on dates. I have columns Status and Progress. The status column have a selection of: Completed, Active and Not Started and the progress column…
    894 6
    894 views 6 comments Most recent by Mike Wilday
  • Formula driven by date
    In Formulas and Functions
    Hi All: I only want this formula to activate only if it is greater than or equally to today's date, but having trouble adding TODAY part of the formula: =IF([% Complete (Actual)]3 > [% Complete (Planned)]3, "Up", IF([% Compl…
    1 5
    1 view 5 comments Most recent by Tony Barthelemy
  • COUNTIFS Formula Help
    In Formulas and Functions
    As one of the pieces of logic in a formula I'm attempting, I want a count within a range (column) if the date (formatted as MM/DD/YY) falls within the year 2017.  What do I need to change in the formula as shown in my screen shot?
    35 7
    35 views 7 comments Most recent by Mike Wilday
  • VLOOKUP Calculation Lag
    In Formulas and Functions
    Hi,   I am having problem with a VLOOKUP process.   Our current process flow is as follows   Import data into a "DataDump" sheet through API, at this point each row of data is assigned a number, matching that of the row num…
    99 3
    99 views 3 comments Most recent by Jim Hook
  • Conditional Alert enhancement status and issue with work around for alerts on incomplete tasks only
    In Formulas and Functions
    Any update on the functionality for the "Reminders" in "Alerts and Actions" to be conditional to eliminate for example tasks based on a completion condition?  I used the work around proposed previously in this forum (se…
    23 1
    23 views 1 comment Most recent by Shaine Greenwood
  • Count Dates / Checkbox Formulas
    In Formulas and Functions
    I am in process of transferring a large excel document into Smartsheet (over 2000 lines).  The excel version is tracking 2007 projects, some of which are complete, some no longer active, and some active.  I need to count how many active pr…
    101 6
    101 views 6 comments Most recent by [email protected]
  • Calculate Win/Loss on a quote log
    In Formulas and Functions
    Hello   I have a quote log that has a win/loss column. I want to populate a cell that takes the $ amount of the won bids, adds them up to show a total $ amount of won bids. Same for lost bids.  I also want this result to auto update if …
    11 3
    11 views 3 comments Most recent by Chris McKay
  • Formula with two conditions met turning a ball green
    In Formulas and Functions
    Hello, I am trying to build a formula that will do the following:   * Yes/no drop down column = Yes * Verification check mark column = checked * Health symbol column turns green If either column is blank the health stays blank.
    12 1
    12 views 1 comment Most recent by Chris McKay
  • Formula Help
    In Formulas and Functions
    Hi,  I am trying to combine two formulas into one, both, are correct on their own but I am unsure on how to combine them. Formula1: =IF([% Complete]7 < ABS(0.6), "High", "Low") Formula2: =IF(Blocked7 = 1, "Hi…
    3 3
    3 views 3 comments Most recent by Chris McKay
  • vlookup error
    In Formulas and Functions
    Hello, I have created a vlookup that works but cannot seem to create a second one.  Working: =VLOOKUP([Currency Code]55, {Customer FY18 FX Rates Range 2}, 4, false) Not working: =VLOOKUP(Country55, {Region List Range 2}, 4, false) I ca…
    157 1
    157 views 1 comment Most recent by Chris McKay