Welcome to the New Smartsheet Online Community
You’ll notice that things have changed a bit. If you need help getting oriented, please take a look at the posts here in the Welcome to the Community category.
Formulas and Functions
Discussion List
-
First post 01/20/20,Last post 01/22/20Hi, I need to compare the hours allocated to the hours actually worked, through timesheets. For this I am using a spreadsheet to aggregate all the data from timesheets in order to extract metrics of hours worked by time and in which proj…Formulas and Functions291soeirorenan
-
First post 01/15/20,Last post 01/22/20I have a Smartsheet that will have values inputted each week and I would like to calculate an historical rolling average based on the last four weeks. What formula would I use to calculate this rolling average? Example data (Assuming c…Formulas and Functions283Joshua Lewis -
First post 01/17/20,Last post 01/22/20Hi, I have a sheet with project workflows. Each workflow has an ID# and they have indented rows. I have a form that enters new rows into the top of the sheet. Everything is working fine as far as auto-filling all the gnarly formulas I…Formulas and Functions352Darren Mullen
-
First post 01/20/20,Last post 01/22/20In Smartsheet, there is a function called Countif(CHILDREN([reference])). This is a great formula but what if I wanted the count of children based on more than 1 condition? I searched in the knowledge base, but couldn't find either a 'coun…Formulas and Functions311Stephane
-
Issues when opening a sheetAnsweredFirst post 01/22/20,Last post 01/22/20Hi, In certain sheets, where many conditional formatting rules, workflows and functions are set, every time I open the sheet it colors all rows in red and it offers me the option to save my changes although nothing has changed in the sheet…Formulas and Functions61Amit Dahan
-
First post 11/27/18,Last post 01/22/20I understand I can likely do this with a very, very long VLOOKUP or IF/THEN formula in every cell, but I'm dealing with over a hundred items and that will be unwieldy. Situation: I would like to have a form where someone selects one op…Formulas and Functions39516Dajah
-
SUMIF by Vendor and DateAnsweredFirst post 01/17/20,Last post 01/22/20I am new to Smartsheets and cannot figure out the formula for a particular SUMIF. Basically, I need the total by Vendor & Year. I have an incredibly long list of invoices that I've imported into Smartsheets, and want to be able to use …Formulas and Functions253cnikkih
-
Formula with multiple equationsAnsweredFirst post 01/21/20,Last post 01/22/20I'm trying to convert a fairly simple formula from excel into one of sheets that has multiple equations in it and can't seem to find the one that works best. formula in excel would be =(B45*B46)+B45Formulas and Functions131Meaghan Dunn
-
First post 01/21/20,Last post 01/22/20Hi, I'm using a template and it already allow me to flag a column when At Risk. I would like to count how many items I have flagged, but i'm not sure how. I've used =SUMIF({Sheet - Request Tracker with Form Range 1}, "Red") =C…Formulas and Functions121Julio Martinez Coronado
-
Duration between Military TimeAnsweredFirst post 01/21/20,Last post 01/21/20What is the easiest formula to use to determine the time difference between two Military Times? I'm using the 4 digit military time and also I need the formula to calculate across midnight. I'd like the time difference to be in minutes (bu…Formulas and Functions217Brittany Hajicek
-
Need to add ISBLANK to a formulaAccepted AnswerFirst post 01/21/20,Last post 01/21/20Good morning, To determine the status of a row, I have the following formula (in the Status) column: =IF(Complete1 = "Yes", "COMPLETED", IF(Complete1 = "Cancelled", "CANCELLED", (IF(TODAY() >= [Du…Formulas and Functions133Stephanie Stewart
-
IF NOT ISBLANK helpAnsweredFirst post 01/21/20,Last post 01/21/20I'm trying to create a formula that will display the different colored dots in the status bar. I had success at first but one part of the formula isn't working with the others. I need it to display BLANK if Patient is blank, Gray if patien…Formulas and Functions193Zachary Mitchell
-
First post 01/20/20,Last post 01/21/20Hello, I am using the following formula to represent health color for individual rows: =IF([Start Date]40 > TODAY(), "Blue", IF(AND([% Complete]40 < 1, [End Date]40 < TODAY()), "Red", IF(COUNTIFS(CHILDREN(), &q…Formulas and Functions144Adam Fink
-
First post 01/21/20,Last post 01/21/20The only option I found is URL and it needs to starts with http. Any help will be appreciated.Formulas and Functions121Juan Castro
-
Report on Last Comment date?AnsweredFirst post 01/20/20,Last post 01/21/20I'm seeing that the System Modified field is NOT updated after entering a new comment on a row. Is there a way to report on the Last Comment Date? I am trying to identify a way to create a report on my sheet that shows Last Update dates, a…Formulas and Functions273Spud Software
-
First post 01/16/20,Last post 01/21/20In our project tracking sheet we have a column for Project/Task Name. The project name is listed in the parent row and then any tasks are listed below as child rows. The projects are added to the sheet by the Director who also assigns a pr…Formulas and Functions2711Melissa.Holland80446
-
vLookup and AVGAccepted AnswerFirst post 01/10/20,Last post 01/21/20Hello, I am trying to do a vlookup to count the average days to fill for each Org Level, by Exempt VS Non-Exempt. This is the source sheet: https://app.smartsheet.com/b/publish?EQBCT=2b4a116ae47c4d17a13c6fea0d751347 Sheet I wanted the Vloo…Formulas and Functions3713styless
-
Create metrics using a Date RangeAccepted AnswerFirst post 01/16/20,Last post 01/21/20Hello - I am trying to build metrics that show how many projects are created with specific quarters based off a Date completed column. Currently using this formula but it is not working: =COUNTIFS({Project List Range 1}, "Closed"…Formulas and Functions243Shane Cornwell
-
First post 01/17/20,Last post 01/21/20I have formulas used to create target finish dates. What I would like is to have the target finish date show the date based on the formula when the Actual finish date column is blank. But when the actual finish date is populated I would l…Formulas and Functions1911AmandaM
-
First post 01/20/20,Last post 01/20/20This discussion has been moved.Formulas and Functions10Alejandra -
First post 01/17/20,Last post 01/20/20Hi Community, I am working with an INDEX MATCH formula to return content from another sheet; however, for each unique value, there are 4 duplicates. For example, an associate completed 4 learning events all attached to the same cohort na…Formulas and Functions235xnickeddlemanx
-
First post 01/20/20,Last post 01/20/20Good afternoon, Would it be possible to create a product that allows users to do a live search on sheets and even possibly aggregate data from multiple sheets to produce given search results. I have already built this through a customized…Formulas and Functions150Anthony Verhoeven
-
First post 01/17/20,Last post 01/20/20Hello community! I am attempting to use an IF statement in a contact column to insert a vendor name depending on the company name in the associated row. =IF(Company1 = "Hospitals", "Vendor Name1", "Vendor Name2&quo…Formulas and Functions198Sldollman
-
Archiving a Child row with it's parent row.Accepted AnswerFirst post 01/17/20,Last post 01/20/20Hello everyone! I want to set up a workflow where I move a row when it check off as archived. But that row is a child of a parent row. How can I move both the child and parent row from my sheet, but also not actually remove the parent row …Formulas and Functions217Hammermill21
-
First post 01/17/20,Last post 01/20/20Hello, I have two columns, one has job numbers in it, and the one beside it has this formula to detect if the first coiumn has duplicates: =IF(COUNTIF([Complete Job Number]:[Complete Job Number], [Complete Job Number]51) > 1, 1). The ro…Formulas and Functions192Mark Strecker -
Formula to have a check box ticked when a date is reachedAccepted AnswerFirst post 01/16/20,Last post 01/20/20Hello Smartsheet Community, Would anybody know how to write a formula to tick a check box once a date in another column is reached. Many thanks in advance😊Formulas and Functions295[email protected]
-
Calculating average difference in dates between two columnsAccepted AnswerFirst post 01/17/20,Last post 01/20/20Hi I am trying to find the average time to complete a task without adding additional columns in my sheet. I would like to set it up in the sheet summary for easy reporting. In excel I would use the following formula. If a cell is blank th…Formulas and Functions201Jeanice Truong
-
First post 03/18/19,Last post 01/20/20Hello I have a parent row on a sheet and I would like new entries that flow into that sheet through a form to be automatically indented so it's a part as a child row to the parent row from the beginning. Also, there is a formula that isn'…Formulas and Functions578Lindsey P -
First post 01/17/20,Last post 01/20/20I am logged into the Community, looking for help on formula using IF(OR. No matter what I try searching for, I get: "No results found." I can search for "if", "No results found." It doesn't matter what I searc…Formulas and Functions141[email protected] -
First post 01/19/20,Last post 01/19/20I have a sheet where we do calculations such as Tasks coming due in the Next 7 days "=COUNTIFS({AF}, <=TODAY(+7), {PComp}, <>1)". When we used the metric widget on the dashboard, it didn't update the numbers each day. …Formulas and Functions121Cheryl Crouse
