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

  • Can Conditional Formatting trigger specific formulas?
    In Formulas and Functions
    The spreadsheet in question is a shipping/inventory database.  The top rows where Device SKUs are listed are the total contracted amount of devices a client pays for. Then as we create shipments (which go under the blue parent rows), we've…
    Answered - Pending Review
    22 5
    Answered - Pending Review 22 views 5 comments Most recent by Paul Newcome
  • Does the COUNTIF Function work if the criterion have decimal places?
    In Formulas and Functions
    I am working on an extended process and I want to sub-categorize a set of steps. Ideally I want to use the 1.1, 1.2, 1.1.1 as step headers but when I try and do functions to count cells it is not working. Example: The COUNTIF functions…
    Answered - Pending Review
    1091 6
    Answered - Pending Review 1.1K views 6 comments Most recent by Paul Newcome
  • LOG Function
    In Formulas and Functions
    Is it possible to calculate (or even approximate) a LOG function in Smartsheet? We regularly use the below formula to calculate A/E fees: (35/LOG(Construction Cost))-1.15)*(Complexity Factor) = A/E fee % I have a sheet setup where someon…
    Answered - Pending Review
    55 1
    Answered - Pending Review 55 views 1 comment Most recent by Paul Newcome
  • How can I compare two cells in a cross sheet reference?
    In Formulas and Functions
    Hello, I have a COUNTIFS formula (below) that returns the number of items that pass all the tests. Basically, count an item if it's unique id is not blank, it's % complete is 100, and the risk area is Antiboycott. This formula works beau…
    Accepted
    131 3
    Answered ✓ 131 views 3 comments Most recent by Paul Newcome
  • Averageif without maximum payment
    In Formulas and Functions
    Hallo, I want to calculate the average damage without considering the maximum damage. I tried this: =AVERAGEIF(Zahlung4:Zahlung140; >1) - MAX(Zahlung4:Zahlung140) but it doesn´t work. I get a - value Any idea ? 🙄
    Accepted
    11 1
    Answered ✓ 11 views 1 comment Most recent by Paul Newcome
  • Is it possible to trigger an alert notification ONLY if the value of a field increases?
    In Formulas and Functions
    I have a formula that counts absences for the past 6 months. A formula in another column looks at the first and when that number reaches 3, it adds an occurrence (value changes from 0 to 1). I've set up automation to notify the supervisor …
    Accepted
    93 3
    Answered ✓ 93 views 3 comments Most recent by Paul Newcome
  • Different types of zeros?
    In Formulas and Functions
    The formula below is creating multiple "zeros". See pictures. What am I missing? =IF([email protected] = "ERROR", "ERROR", IF([email protected] = "TBD", "TBD", IF([email protected] = "DEDUCT", &q…
    Accepted
    33 5
    Answered ✓ 33 views 5 comments Most recent by Paul Newcome
  • IF(OR with RYGG
    In Formulas and Functions
    I would like to automate a RYGG column, assuming that my team is entering there own start and finish dates but due dates will be created by senior staff. Yellow=In progress Red=Overdue Grey = Not started Green=Completed So IF start date …
    Accepted
    33 7
    Answered ✓ 33 views 7 comments Most recent by Paul Newcome
  • Calculation in column is not auto-populating
    In Formulas and Functions
    I have a spreadsheet with a calculated column (If statement based on previous column). I regularly paste in new rows, but the calculated columns do not always auto-populate. When I hover over the calculated cell, the calculations are prese…
    Answered - Pending Review
    23 3
    Answered - Pending Review 23 views 3 comments Most recent by Paul Newcome
  • SUMIFS
    In Formulas and Functions
    I am trying to create a SUMIFS formula that will allow me to pull data from the following columns: Region: APAC (There are several offices that make up the APAC region) OfficeStatus: Closed OfficeHeadCount: Sum Total (For example 8 APA…
    Accepted
    33 3
    Answered ✓ 33 views 3 comments Most recent by Paul Newcome