Formulas and Functions

Formulas and Functions

Discussion List

  • Kal-El
    Kal-El 44 views 5 comments Most recent by Kal-El
    First post 02/21/18,Last post 02/22/18
    Good day All: I am trying to use a SUMIF formula that would allow me to add everything less than 2,000 with multiple status. Here is the formula I am using but I am receiving an invalid operation. what am I missing?   =SUMIFS({A3: Fi TL…
    Formulas and Functions445
    Kal-El
  • Chris Ross
    Chris Ross 16 views 1 comment Most recent by Jim Hook
    First post 02/21/18,Last post 02/21/18
    I want to be able to average, or other function, over a subset of the cells in a row.  The number of cells I want to include is specifiied in a separate cell.   In Excel, I accomplished this with OFFSET.  I read another article here noti…
    Formulas and Functions161
    Chris Ross
  • celento
    celento 96 views 3 comments Most recent by J. Craig Williams
    First post 02/20/18,Last post 02/21/18
    Is it possible to do an Index and Match referencing another sheet? My formula works when I am using data on the same sheet but when I try to reference a different sheet it does not work.  
    Formulas and Functions963
    celento
  • Mikael Gustafsson
    Mikael Gustafsson 16 views 2 comments Most recent by J. Craig Williams
    First post 02/21/18,Last post 02/21/18
    Hi,   I have a sheet with two date columns. I want to count the rows where only one of the date columns have a value. I´ve tried to create a third column with a formula that sets it to 0 or 1 depending on whether the second date column I…
    Formulas and Functions162
    Mikael Gustafsson
  • alex_bch
    alex_bch 23 views 2 comments Most recent by alex_bch
    First post 02/16/18,Last post 02/21/18
    Hi, I've created a rather complicated cross-sheet formula that counts cells from a different sheet using 3 criterias. One is the description that starts with a text mentioned in a cell above. I've tried to enter the search criteria into t…
    Formulas and Functions232
    alex_bch
  • bhope51
    bhope51 13 views 1 comment Most recent by Shaine Greenwood
    First post 02/15/18,Last post 02/20/18
    Hello,   We are currently using a web form for customers to submit a service request. After the form has been submitted a auto-numbering confirmation is generated and the customer is sent a notification with that confirmation number. Whe…
    Formulas and Functions131
    bhope51
  • scott.barrett
    scott.barrett 93 views 5 comments Most recent by Andrée Starå
    First post 02/12/18,Last post 02/20/18
    In our main sheet, we have several simple Sum formulas for numbers and some for dates.  We have several reports created that contain columns with the formulas.  While in reports, you can change some data in each cell that will carry back t…
    Formulas and Functions935
    scott.barrett
  • Lori Khoury
    Lori Khoury 41 views 6 comments Most recent by Mike Wilday
    First post 02/17/18,Last post 02/19/18
    Hi  I am trying perform the COUNTIFS function and can't get this to work.  So what I need to count is the following * Count if criteria is "s" in the function column * Count if criteria is "Med" in the priority column…
    Formulas and Functions416
    Lori Khoury
  • mheaps38436
    mheaps38436 29 views 1 comment Most recent by Mike Wilday
    First post 02/19/18,Last post 02/19/18
    I'm trying to set the RYG status of a task based on End Date and % Complete of the task.  I've put a formula in place using =IF(AND(...  but I keep getting the error "# INCOMPLETE ARGUMENT SET" Below is the formula, can anyone t…
    Formulas and Functions291
    mheaps38436
  • Debbie J
    Debbie J 102 views 5 comments Most recent by Mike Wilday
    First post 02/16/18,Last post 02/19/18
    If cell A3 contains "ABC" then enter "ABC" in cell A1. Can someone help me do this in smartsheet?   I tried using =If(find("ABC",A3,)="ABC","ABC","X")  but its not working.   …
    Formulas and Functions1025
    Debbie J
  • MariaF
    MariaF 69 views 1 comment Most recent by Mike Wilday
    First post 02/18/18,Last post 02/19/18
    Hello, I'm trying to minus days from dates using the following formulas but am getting errors everytime: =NETWORKDAY([Date Printed]8 - [Date Dispatched]8) - gives me the error #Incorrect Argument Set =NETWORKDAY([Date Printed]8, [Date D…
    Formulas and Functions691
    MariaF
  • melissa34381
    melissa34381 33 views 1 comment Most recent by Mike Wilday
    First post 02/17/18,Last post 02/19/18
    I think I'm close but this is driving me crazy.  I have an event code column and two other columns where I am collecting participation data on Saturday and Sunday.  I would like to know the total participation for the weekend. The formul…
    Formulas and Functions331
    melissa34381
  • Matthew@SIL
    First post 02/18/18,Last post 02/18/18
    I folks. I think I found an edge-case in your new cross-sheet formula system. I wrote some index formulae to pull related data from other sheets when the user chose some options from a list. All was working, and I was able to link, custom…
    Formulas and Functions321
    [email protected]
  • gdavid.theflyer
    gdavid.theflyer 50 views 1 comment Most recent by Shaine Greenwood
    First post 02/15/18,Last post 02/16/18
    I am working on charting out dev tasks and need to display what our expected total monthly cost would be based on the tasks finished each month. I am struggling to find out a good way to have this either permanently displayed or easily acc…
    Formulas and Functions501
    gdavid.theflyer
  • Thane Knutson
    Thane Knutson 124 views 3 comments Most recent by J. Craig Williams
    First post 02/13/18,Last post 02/16/18
    With the recent expansion of formulas to cross reference other sheets I would love to be able to split projects to other sheets. If I could pull the Primary column and the Start Date column via formula I could have secondary sheets that ha…
    Formulas and Functions1243
    Thane Knutson
  • Suresh Shenoy
    Suresh Shenoy 16 views 1 comment Most recent by rjudenberg
    First post 02/16/18,Last post 02/16/18
    I have an Invoice Smartsheet which has invoice amount for invoices for last several months. I have to calculate month to day invoice amount. I have given the following formula: SUMIF([Inv Amt Rs]:[Inv Amt Rs], [Inv Date]:[Inv Date] > &…
    Formulas and Functions161
    Suresh Shenoy
  • lisa. caldwell37926
    lisa. caldwell37926 262 views 1 comment Most recent by rjudenberg
    First post 02/16/18,Last post 02/16/18
    I have a hard keyed number in a cell (113), in cell to right is a formula =COUNTIF(Status3:Status97, OR(@cell = "Active", @cell = "Invited")), resulting in a count of 93.  I am trying to subtract the 113 from 93, with …
    Formulas and Functions2621
    lisa. caldwell37926
  • Jim Hook
    Jim Hook 28 views 4 comments Most recent by Jim Hook
    First post 02/07/18,Last post 02/16/18
    As my Smartsheet app has gotten more complex I often try to find ways to simplify sheets and speed things up. The new cross-sheet reference capability has me wondering if it would be faster than the existing links when loading a sheet? I d…
    Formulas and Functions284
    Jim Hook
  • Paul Charlebois
    First post 02/12/18,Last post 02/15/18
    Hello,   I have a particular problem, I have a series of cells that have formulas to detect the checked values of a series of Check Boxes, creating conditional formatting and inserting 'N/A' in certain cells: =IF([Assemblage Mecanique]8…
    Formulas and Functions292
    Paul Charlebois
  • americansurveymanagement35236
    americansurveymanagement35236 152 views 5 comments Most recent by ricki
    First post 02/15/18,Last post 02/15/18
    Would anyone have a example of a formula to count checked boxes in a column divided by the number of unchecked boxes. Any help will be greatly appreciated. Thanks.
    Formulas and Functions1525
    americansurveymanagement35236
  • Kal-El
    Kal-El 15 views 2 comments Most recent by Kal-El
    First post 02/14/18,Last post 02/15/18
    Good afternoon Smartsheet Community:   I am trying to add the total from the count of 2 sheets given the criteria. However, I am receiving an INCORRECT ARGUMENT SET when using SUM or Count conditions (formula below). Any help appreciate…
    Formulas and Functions152
    Kal-El
  • DV
    DV 30 views 1 comment Most recent by Jim Hook
    First post 02/14/18,Last post 02/14/18
    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…
    Formulas and Functions301
    DV
  • Mary Ayers
    Mary Ayers 70 views 5 comments Most recent by Mary Ayers
    First post 02/14/18,Last post 02/14/18
    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 …
    Formulas and Functions705
    Mary Ayers
  • brandon.elmer
    brandon.elmer 16 views 3 comments Most recent by Mike Wilday
    First post 02/13/18,Last post 02/14/18
    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…
    Formulas and Functions163
    brandon.elmer
  • brandon.elmer
    brandon.elmer 53 views 1 comment Most recent by claytonrlewis
    First post 02/14/18,Last post 02/14/18
    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?  (Image)
    Formulas and Functions531
    brandon.elmer
  • EvaM
    EvaM 196 views 1 comment Most recent by Shaine Greenwood
    First post 02/12/18,Last post 02/13/18
    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…
    Formulas and Functions1961
    EvaM
  • Diane Martell
    Diane Martell 15 views 1 comment Most recent by Shaine Greenwood
    First post 02/11/18,Last post 02/13/18
    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…
    Formulas and Functions151
    Diane Martell
  • hquinn
    hquinn 14 views 3 comments Most recent by Shaine Greenwood
    First post 02/06/18,Last post 02/13/18
    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…
    Formulas and Functions143
    hquinn
  • DanielGD
    DanielGD 33 views 3 comments Most recent by Mike Wilday
    First post 02/09/18,Last post 02/13/18
    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…
    Formulas and Functions333
    DanielGD
  • brandon.elmer
    brandon.elmer 242 views 3 comments Most recent by Mike Wilday
    First post 02/09/18,Last post 02/13/18
    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…
    Formulas and Functions2423
    brandon.elmer