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

  • sum with new vlookup sheet reference
    In Formulas and Functions
    I have this formula copied down a column: =vlookup(A1,ReferenceAnotherSheet,3). The first row bring back the correct number value. The other rows are bringing back a #nomatch. Also, I want the cell to return the total of all cells in co…
    DV Profile Photo DV
    51 1
    51 views 1 comment Most recent by Jim Hook
  • IF/AND and cross-sheet reference
    In Formulas and Functions
    I'm trying to write an IF/AND statement that references a range of data in another Smartsheet but an #INVALID OPERATION message is returned. * IF the user is requesting to use the Immersive Learning Center * AND the user is requesting to …
    233 5
    233 views 5 comments Most recent by Mary Ayers
  • Ignore Summary Task When Counting Late Tasks
    In Formulas and Functions
    I use the formula when counting late tasks:  =IF(ISBLANK(Finish107), "", IF(AND(TODAY() > Finish107, [% Complete]107 < 1), 1, "")) The issue with this is that it also counts summary tasks which really aren't task…
    31 views 3 comments Most recent by Mike Wilday
  • Count number of unchecked boxes
    In Formulas and Functions
    To count the number of unchecked boxes I'm using the formula:  =COUNTIF([% Complete]3:[% Complete]60, 0).  The issue is that it is also counting blank rows.  How do I get it to only count actual blank checkboxes? 
    104 views 1 comment Most recent by claytonrlewis
  • IF Function Due Date
    In Formulas and Functions
    I am attempting to create a project status column that is using the Harvey Balls (red, yellow, green and gray). I am wanting these to automatically change yellow when my due date column is 120 days with the due date, I would like it remain…
    444 1
    444 views 1 comment Most recent by Shaine Greenwood
  • Auto Sum last 6 columns
    In Formulas and Functions
    Hello ~ I'm calculating attendance and want to highlight inactive participants. Currently, I copy and paste  =COUNTIF([20 1/6/18]3:[25 02/10/18]3, "P") each week as a new column is added. Is there a formula that will auto add ne…
    12 views 1 comment Most recent by Shaine Greenwood
  • Formulas Including New Rows
    In Formulas and Functions
    Hi,  (Sorry if this has already been asked and resolved, I couldn't find anything).  I currently use smartsheet for our team's training log. In doing so, there are several columns that require numerical data that I would like to keep tra…
    12 3
    12 views 3 comments Most recent by Shaine Greenwood
  • Invalid Data Type error
    In Formulas and Functions
    Getting an Invalid Data Type error with this formula: =COUNTIF(AND(FacilityCount:FacilityCount, "TZ"),NOT(COUNTIF(Status:Status, "Green"))) The second "CountIf" is a symbols column.  Looking to make a count…
    41 3
    41 views 3 comments Most recent by Mike Wilday
  • Nested IF function - Ignore Blank Cells
    In Formulas and Functions
    I'm trying to count the number of late tasks in a project plan.  The formula I'm using works as long as a row isn't blank.   =IF(AND(TODAY() > Finish15, [% Complete]15 < 1), 1, "").  If I drag the formula all the way down t…
    821 views 3 comments Most recent by Mike Wilday
  • COUNTIF Formula for Only Showcasing What is Due to Upcoming Week Period
    In Formulas and Functions
    I'm trying to determine a COUNTIF formula to show what deliverables are due in the next 7 day period based upon a Due Date column. However, I'd like said formula to only showcase what deliverables are in the next 7 days and ALSO be able to…
    284 views 2 comments Most recent by [email protected]