Formulas and Functions
Archives for October 2018
Discussion List

First post 10/31/18,Last post 11/01/18Hi Community, I am attempting to create a KPI for a dashboard that will capture the average number of days a 'something' has been opened, but only if it is still in an opened state. I can perform the appropriate "days open" wit…Formulas and Functions322Beemer Bum

First post 10/31/18,Last post 11/02/18I've got formulas in my sheet that calculate due dates that fall on weekends, and I've used conditional formatting to highlight those dates to alert me to those dates that are on Saturdays and Sundays. Now, I want to move those dates to t…Formulas and Functions253ksmith56491

First post 10/31/18,Last post 11/01/18Hi all, I am having an issue that might be simple to solve, but I can't seem to find an efficient solution. I have 2 spreadsheets:  One with the ID, name, Lot and several other columns with product specification information  Another …Formulas and Functions22Jose da Silva

First post 10/31/18,Last post 11/01/18I am looking for help with a formula where I have a parent row and then multiple children rows below. The Parent has an overall status that is a 4 ball symbol of red, yellow, green, blue) My child rows are using a dropdown menu called &qu…Formulas and Functions4712h1pst3r

First post 10/31/18,Last post 11/02/18Hi all, Probably my incompetence here but i have a confusing issue at the minute. I have a long list of job numbers and am trying to sort them in numerical order. There is a criteria that we use to create these that you should probably …Formulas and Functions286grant53396

First post 10/31/18,Last post 11/01/18I have a cross sheet table with data displayed on it that works fine. Just wondering how I can display a trend arrow using a function for Up, Down and Across. Basically, I am measuring KPI performance against the number of days since t…Formulas and Functions1055Steve Moss

First post 10/30/18,Last post 10/30/18Greetings, Currently at row 1600, waiting for a cell's formula to process the formula may take up to 10 minutes. For instance, sometimes when I change the duration of a Child row by 1 day, it may take 10 minutes for the End Date to advan…Formulas and Functions12Dan Seidler

First post 10/30/18,Last post 10/30/18I'm trying to figure out how to create a formula that has a red flag appear if the following are true. I can't seem to figure out how to put the whole formula together. Any help is appreciated. Thanks! IF(AND[Due Date]@row < TODAY(), S…Formulas and Functions151Jennie O

First post 10/30/18,Last post 11/02/18Hi There I'm very new to smartsheet so for you this might be a very simple thing. I'll appreciate if someone let me know what'd be the formula if I want to raise a flag whenever the due date is 30 days from the current date. We'll apprec…Formulas and Functions602kiani

First post 10/30/18,Last post 05/07/19I am trying to get a count of the number of rows with dates that meet one or another condition. I know I cant use Countif so I planned on adding a column next to my date column to capture the data. One condition is that the date is either…Formulas and Functions2005Phil B.

First post 10/30/18,Last post 10/31/18I have an internal client who wants to parse a list of 100 different zip codes, and depending on the zip, assign another text field into a different field. In other words, IF zip code = 11733 OR 11725 OR 11890 (and so on) THEN assign to N…Formulas and Functions123gbarry67446

First post 10/29/18,Last post 10/30/18Hello, We have here 5 different sheets containing records for Medical service details, including columns for patient name, ID, DOB, charge amount, payment status, etc. Now i want to get a sixth sheet that will look up from the above 5 s…Formulas and Functions426Chaim T

First post 10/29/18,Last post 10/31/18I am trying to count the number of projects completed between July 1, 2018 and June 30, 2019 with this formula: =COUNTIFS({Status}, ="Completed", {Completion Date}, >=DATE(2018, 7, 1) <= DATE(2019, 6, 30)) The outcome …Formulas and Functions22hennarizvi

First post 10/29/18,Last post 01/17/20I have a column set as Contact List and the Allow Multiple Contacts per Cell field is checked. I am using COUNTIF statements to count how many people are assigned to the tasks for a dashboard. I am using the formula: =COUNTIF({Project L…Formulas and Functions42326BryanHeft

First post 10/29/18,Last post 03/09/19Hi everyone, I am trying to figure out a formula, that will show the following result: * If the completion date is before the due date, the green circle appears; or * If the completion date is after the due date, the yellow circle appear…Formulas and Functions509Tammy Wombwell

First post 10/28/18,Last post 10/30/18I want to create a COUNTIF function that will count only the Parent row of a status column in a project tracking sheet. This is to create a metric sheet that will show all project statuses by each project manager's sheet. There is a status…Formulas and Functions2423cody

First post 10/27/18,Last post 04/05/19Hello, I don't have a pivot table app so I am looking for formula help. I have a grid that has a column called "Goal". Users and choose a value from the drop down or type in their own value in this field. I need to be able to…Formulas and Functions717cyndi2403

First post 10/26/18,Last post 10/27/18Does anyone know how do you split data in one cell into multiple columns?Formulas and Functions31Stephanie Taylor

First post 10/26/18,Last post 10/26/18Greetings, I have a organizational structure that has three levels * Campaign * Project * Deliverable * Task I am trying to figure out a way how I can have a column with one of the above values that is based on hierarchy. I have the…Formulas and Functions571Jeremy_Busch

First post 10/26/18,Last post 11/01/18Hi All, I have looked around on multiple forms and haven't been able to find a solution for why my formula isn't working.I am trying to determine the formula for the total project % complete of the children rows. For the Project Initi…Formulas and Functions23212Marcia Drabek

First post 10/25/18,Last post 10/26/18Hi everyone! Im having trouble with a simple COUNTIF formula. I am getting "UNPARSEABLE" but for the life of me, I just don't know what I'm doing wrong. I want to count how many times the "Weekly Goal" was met in a r…Formulas and Functions123lisarae723

First post 10/25/18,Last post 10/26/18Hi  I've seen this question a few times, but I am still not able to get this to work. I have a Metrics page that I am rolling totals up to so I can display a donut chart of projects per GEO. This has worked, up until now, because I ha…Formulas and Functions173TKeller

First post 10/25/18,Last post 10/26/18I would like to use the direction symbol (up,down,sideways) in a formula if =IF([ISSBacklog]1 < [ISSBacklog]2,“Down”) but it is not working. Any help would be greatly appreciatedFormulas and Functions141vlambert

First post 10/25/18,Last post 10/26/18Hello, I am working on providing dashboards for my seniors. Here is the premise: I have multiple project managers (PMs) that work with four different agencies. Each PM has their own assigned sheet and assign an agency to the project. To…Formulas and Functions131henryling

First post 10/25/18,Last post 10/26/18I have a formula which works out the Net Days since an event occurred. I have the following: [Event Type], [Days Since Last Event], [Day Before], [Trend] The [Event Type] is the event I am searching for the number of days since the la…Formulas and Functions201Steve J Moss

First post 10/25/18,Last post 10/26/18Hi there I have this formula built to count the number of projects completed within a certain date range: =COUNTIFS({Completion Date}, >DATE(2017, 7, 1) <= DATE(2018, 6, 30)) However, there are five different stages of a…Formulas and Functions461hennarizvi

First post 10/25/18,Last post 10/25/18Hello, Was hoping someone could see what i am doing wrong here; i am attempting to take the value of two cells and average them. Both are percentages. i use the formula =AVG([average calc]33, [average calc]34) and i get the "DIVID…Formulas and Functions4012dana y

First post 10/25/18,Last post 10/25/18I want a new column to reflect the date 7 days before an existing due date column. What's the formula?Formulas and Functions173tylermh

First post 10/25/18,Last post 10/26/18Hello, I'm trying to write a formula that will count rows containing a date that is part of the current work week. The date column has non date values in a few cells which seems to be throwing off my formula. This is what I have so far; …Formulas and Functions622SavB.

First post 10/25/18,Last post 10/25/18I'm creating a sheet to record the use of agency/contracting staff across a site (once the proof of concept is done it'll be expanded to cover multiple sites), where entries will be added to the sheet below via form. The "within budge…Formulas and Functions151Garmon D