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

  • unexpected behavior with collect formula
    In Formulas and Functions
    I believe this formula should work, and when I break it apart it seems to work but doesn't when together.   Copy from sheet =JOIN(COLLECT({Range 1}, {Range 2}, IF($[By Shift]$1 = "All", @cell = @cell, @cell = $[By Shift]$1), {…
    91 7
    91 views 7 comments Most recent by L_123
  • Formula Assistance
    In Formulas and Functions
    I am attempting to automate my project list by scoring each project based on calculations in another sheet. I would like to create a "Project Score"  based on drop-downs in my project sheet.   Sum=Complexity*weight + cost*weight…
    162 3
    162 views 3 comments Most recent by Paul Newcome
  • Ignoring blanks in Countifs
    In Formulas and Functions
    I am trying to create a formula to look at multiple columns and count if they meet a specific criteria.  I have a formula that works when there is data in the sheet but it causes an error whenever data is missing.  I need to create a templ…
    908 8
    908 views 8 comments Most recent by Mike Wilday
  • At Risk Flag
    In Formulas and Functions
    Good Day All: I am trying to add to the following formula below. If my Due Date (Planned) is within 1 day (i.e. due 04/12/18) and % Complete (Actual) is less than 50%, it should be Flagged. =IF(Duration3 > 0, IF(OR(AND(TODAY() > [D…
    282 9
    282 views 9 comments Most recent by Steve Rogers
  • Index(Collect add to formula page
    In Formulas and Functions
    Can we get an index(collect example added to the collect formula page? I have found it extremely useful in finding specific iterations of data, and I only stumbled upon it trying random things to solve a specific issue I had a while back. …
    11 1
    11 views 1 comment Most recent by Shaine Greenwood
  • formulating dates excluding weekends
    In Formulas and Functions
    I have 3 columns with different dates. My goal is to enter 1 date, and have the other 2 dates formulated based off the 1st date. I created a formula to satisfy this need, but I am ending up with some dates falling on weekends, which I cann…
    21 1
    21 views 1 comment Most recent by Paul Newcome
  • Determining an identifier based on parent/children and an additional factor
    In Formulas and Functions
    I have a sheet where I'm tracking IT initiatives, and some are considered Cross-Platform. Some Cross-Platform records have children and others do not. (See screenshot) Based on some reports I need, I have to identify Cross-Platform initia…
    12 1
    12 views 1 comment Most recent by Paul Newcome
  • COUNTIFS for individual weeks
    In Formulas and Functions
    I reached out for assistance recently about formulas to count specific projects for individual weeks.  The formula assistance I received counts projects from today + next 7 then 13 days then 21 days. What the formulas are not doing is onl…
    1 1
    1 view 1 comment Most recent by Paul Newcome
  • Using formulas to determinate the status of a set of tasks
    In Formulas and Functions
    Hi community,  I'm new on Smartsheet and I'm trying to make a formula to automatically calculate the status of a set of tasks (or subtasks). Let me explain my situation: I've created a main task, lets say "Project1" and it cont…
    2 1
    2 views 1 comment Most recent by Paul Newcome
  • SUMIFS function
    In Formulas and Functions
    I would like to the sum of one column based on a specific date range in another column. e.g Need to total costs for June 2018.  I am using the following formula =SUMIFS(Cost3:Cost29,[End Date]3:[End Date]29,”>=01/06/2018″,[End Date]3:…
    101 5
    101 views 5 comments Most recent by Paul Newcome