Formulas and Functions

Stumped by a formula? Ask for help or find examples to see how others use a formula to solve a similar problem.

Discussion List

  • Index and Match
    In Formulas and Functions
    Hi , How to use Index and Match function in SS.Please advise with an example  
    8922 1
    8.9K views 1 comment Most recent by Paul Newcome
  • WEEKNUMBER from two columns
    In Formulas and Functions
    Hi All, I have two columns where one column will have a DATE and the other one will be blank. It will never happen that both columns will have a date in them. Now I want Smartsheet to check which two cells has a Date and show WEEKNUMBER…
    13 3
    13 views 3 comments Most recent by Paul Newcome
  • Need an HLOOKUP or similar
    In Formulas and Functions
    Does anyone have a formula that will evaluate all the cells in a row, then return the column name for the cell that has a particular value? Here's what I am trying to do. I have a Smartsheet with 23 columns, of which 5 are due dates. A 6t…
    559 1
    559 views 1 comment Most recent by Paul Newcome
  • SUMIFS
    In Formulas and Functions
    I am trying to sum accounts receivable for individual salesmen. I have gotten to the point where I can add if the check box under [Check Received] is marked but I do not know how to sum if a check box is unmarked. So far my formula is retu…
    1 1
    1 view 1 comment Most recent by Mike Wilday
  • COUNTIF Using Open-Ended Range Formula (Not Working)
    In Formulas and Functions
    I'm using the COUNTIF formula and am trying to set an open-ended range formula (see below). When I do so, I receive the error #UNPARSEABLE =COUNTIF(Test Result:Test Result, Status1) Is there any reason this wouldn't work? I've attached …
    4 2
    4 views 2 comments Most recent by mrsmorden
  • Transform time in minutes
    In Formulas and Functions
    Our Smartsheet has been build with time entered that way 2h00 0h15 0h15 1h00 3h00 2h00 1h00 0h15 0h15 3h00 0h30 18h00   I want to transform that value automatically in minutes (120 / 15 / 15 / 60 / 180 / 120 etc)   Any idea…
    227 6
    227 views 6 comments Most recent by bcwilson.ca
  • % complete based on count of completed pieces
    In Formulas and Functions
    Hi,  I'm running a project where I want to report our the % completion of a sub task based on the # of pieces completed. I'm thinking I need to create a formula as the % of completion functionality looks at duration vs. actual amount of w…
    4 1
    4 views 1 comment Most recent by Paul Newcome
  • Nested IF , logical operator (AND)
    In Formulas and Functions
    Hi All, I am trying to build a formula with IF, AND conditions but i am unsuccessful, can any one help me to sort out this please.  Here is the forumula =IF(AND([Position at Practice]227 = "Receptionist", [Current MECA Step]22…
    208 5
    208 views 5 comments Most recent by Andrée Starå
  • % Complete range in If statement how best to describe
    In Formulas and Functions
    I'm trying to have a conditional format occur in a status column, when the % Complete column hits a certain condition, for example: 1.  If % Complete = 100%, place "Complete" in the Status Column, and conditional format will cha…
    93 1
    93 views 1 comment Most recent by Andrée Starå
  • Dynamic Future Date based on Condition
    In Formulas and Functions
    Hello,  Issue: I need to create a date column that will produce a forecasted date, based on a manually entered date plus X amount of days, based on a certain value (phase). Example:  If Phase is "SOW Draft/Negotiations", add 60…
    14 1
    14 views 1 comment Most recent by Paul Newcome