Formulas and Functions
Archives for August 2020
Discussion List

Using COUNTIFSQuestionFirst post 08/09/20,Last post 08/09/20Hi, I am using the following formula to count those occasions when CSOD (unit) is impacted by CFCD (unit) and the status of the impacts is either 'conceptual' or 'complete' and it does not work. If I limit it to only counting 'conceptual'…Formulas and Functions50Phil Wightman

Question Cristina Andersen 6 views 0 comments 0 points Started by Cristina Andersen Formulas and FunctionsFirst post 08/08/20,Last post 08/08/20Hi all, here's my scenario. I have four sets of cars that get moved out of my location. They come sporadically throughout the month. Im trying to figure out a formula that would average the turn time on a particular set of cars. Any ideas?…Formulas and Functions60Cristina Andersen

First post 08/08/20,Last post 08/08/20Hi guys, I'm a new Smartsheet partner trying to develop a custom solution for a client. So the client requests Dashboard with up to date leaderboard on it. I've made the leaderboard and connected it to other data so it calculates victories…Formulas and Functions90Leonard Eldić

Question aschneiderheinze74206 9 views 0 comments 0 points Started by aschneiderheinze74206 Formulas and FunctionsFirst post 08/08/20,Last post 08/08/20I have two Sheets, one with a list of full course master names (e.g., ABS435BL6W1DMaster), and another sheet with a list of course sections (e.g., ABA 435 BL 6W). Is there a way to use CONTAINS in a crossreference formula to check al…Formulas and Functions90aschneiderheinze74206

Automate UAT Test ScriptQuestionFirst post 08/08/20,Last post 08/08/20I am looking for a way to create a test script using forms. Multiple users would complete the script which would populate a single sheet that could be reported off...... If a tester completes a step in the script (form1), they would subm…Formulas and Functions60Sandra Healey

Checkbox Column + % Complete Column FormulaAccepted AnswerFirst post 08/07/20,Last post 08/07/20Smartsheet I am trying to use this Formula to calculate % Complete based on Checkbox, however the Checkbox is a different Column Called Done, not under the % Column. Can I do that? Existing Formula: =ROUND(IF(COUNTIF(DESCENDANTS(), 1) / CO…Formulas and Functions94Ray B

Count completed dates once from two columns counting only the most recent competion date by weekQuestionAnswered  Pending Review Lynn Strahm 9 views 2 comments 0 points Most recent by lewis hamilton Formulas and FunctionsFirst post 08/07/20,Last post 08/08/20I'm trying to count the number of quotes completed by week. The challenge is there are two departments who may work on a quote and each enter their own completion date. Sometimes only one department works on a quote so only one of the tw…Formulas and Functions92Lynn Strahm

Answered  Pending Review saraha83081 14 views 2 comments 0 points Most recent by lewis hamilton Formulas and FunctionsFirst post 08/07/20,Last post 08/08/20Hello, I'm trying to create a formula that will display a specific value if a date falls within a certain range. Any assistance would be greatly appreciated. If “Target Update or Replacement Date” is between 10/01/20 and 09/30/21, then &…Formulas and Functions142saraha83081

Sheet Summary  SUM Chilldren Rows based on Status and DatesAccepted AnswerAccepted Answer Davis Dye 12 views 4 comments 0 points Most recent by Davis Dye Formulas and FunctionsFirst post 08/07/20,Last post 08/07/20Good Afternoon! I am trying to sum children rows where certain status' exist where the PickUp Date is today, within the next two days, and in the future beyond that. (Really, 3 formulas). (Image) I tried applying the following formula bef…Formulas and Functions124Davis Dye

Question Nikki McIntosh 9 views 0 comments 0 points Started by Nikki McIntosh Formulas and FunctionsFirst post 08/07/20,Last post 08/07/20Hello. I'm new to formulas and need help. I'm trying to figure out how to get the total % complete for a column of check boxes. We need to know the % complete by milestone (which we figured out) AND the total % complete. We use parent rows…Formulas and Functions90Nikki McIntosh

Date Range FormulaAccepted AnswerAccepted Answer Cassie Sprague 9 views 2 comments 0 points Most recent by Cassie Sprague Formulas and FunctionsFirst post 08/07/20,Last post 08/07/20Hi, I'm trying to set up a formula to count the number of requests that were submitted between a specific date range. Below is the formula that I've written, however I'm still receiving an invalid operation error. Can anyone help me with t…Formulas and Functions92Cassie Sprague

COMPARE COLUMNSQuestionAnswered  Pending Review Sebastian Avila 9 views 1 comment 0 points Most recent by [email protected] Formulas and FunctionsFirst post 08/07/20,Last post 08/07/20Hi have two columns with ID´s and i want to compare them see, if an Id number has a match or a mismatch in the other column. What kind of formula de you recomend to do this? I used Vlookup: =VLOOKUP([NÚMERO DE SINIESTRO]1; {SINIESTROS CF};…Formulas and Functions91Sebastian Avila

First post 08/07/20,Last post 08/07/20I am trying to reference an automated creation column from sheet A to populate date AND time in a column in sheet B. I have been successful in the reference, but then have to format the receiving column to date format due to invalid data…Formulas and Functions90Emerus CCC

Question Lindsey Cunningham 8 views 0 comments 0 points Started by Lindsey Cunningham Formulas and FunctionsFirst post 08/07/20,Last post 08/07/20I am trying to create a budget sheet that roll up the information inputed by mangers on 10 different sheets. Each manager has a grid like the one below and I want to duplicate that as a summation of information from each sheet. So a summat…Formulas and Functions80Lindsey Cunningham

Answered  Pending Review Erin Becker 9 views 1 comment 0 points Most recent by Sean Morgan Formulas and FunctionsFirst post 08/07/20,Last post 08/08/20Hello there, I have a sheet setup with 4 columns of dated. Project Owner Completion Deadline, Project Owner Actual Completion Deadline, Project Auditor Completion Deadline, and Project Auditor Actual Completion Deadline. I am trying to fi…Formulas and Functions91Erin Becker

Answered  Pending Review Jason Jones 18 views 2 comments 0 points Most recent by Jason Jones Formulas and FunctionsFirst post 08/07/20,Last post 08/07/20I have been working on this for hours and can’t seem to figure it out and can’t find proper support on line. This one is a time sensitive metric I am trying to get on our dashboard for our stakeholders to see. Any help you can give me woul…Formulas and Functions182Jason Jones

Answered  Pending Review Sara Downing 12 views 1 comment 0 points Most recent by Bill Brendle Formulas and FunctionsFirst post 08/06/20,Last post 08/06/20I am trying to automate the "Status" column to display the following based on the "Original Action Due Date" column: Coming due in greater than 60 days  Green Coming due in 60 days  Yellow Past due  Red I am also try…Formulas and Functions121Sara Downing

Answered  Pending Review Ray B 9 views 4 comments 0 points Most recent by [email protected] Formulas and FunctionsFirst post 08/06/20,Last post 08/07/20Smartsheet Community: I am trying to Count If the Checkbox is Checked, the Date is Less than August 5 or Blank. I know I am close but can not see finish the Formula Correctly. Current Formula Below: =COUNTIFS({Issue / Defect UAT LOG Range …Formulas and Functions94Ray B

Question Nikki McIntosh 13 views 0 comments 0 points Started by Nikki McIntosh Formulas and FunctionsFirst post 08/06/20,Last post 08/06/20Hello and please help! I'm trying to figure out how to get the total % complete for a column of check boxes. We need to know the % complete by milestone (which we figured out) AND the total % complete. We use parent rows as headers that sh…Formulas and Functions130Nikki McIntosh

SUMIFS FormulaQuestionAnswered  Pending Review Irina Iatco 16 views 3 comments 0 points Most recent by Irina Iatco Formulas and FunctionsFirst post 08/06/20,Last post 08/07/20Hi, I am trying to sum the numbers in the first column where the states are CA, CO, DC and IA. Could you please help. I a still learning formulas in Smartsheet. Thanks! (Image)Formulas and Functions163Irina Iatco

Conditional Sum FormulaQuestionAnswered  Pending Review Anoop Mishra 6 views 1 comment 0 points Most recent by Michael Heisler Formulas and FunctionsFirst post 08/06/20,Last post 08/06/20(Image) Hello, I am trying to insert a formula for extended price of projects that auto calculates for close rating of A, B and C. Any help is much appreciated.Formulas and Functions61Anoop Mishra

Can you help with a counting formula question?Accepted AnswerAccepted Answer Jeffrey Metivier 24 views 4 comments 0 points Most recent by Jeffrey Metivier Formulas and FunctionsFirst post 08/06/20,Last post 08/06/20Hi  I'm trying to create a count if formula that will include Bid Stage from one column and either of Open or On Pause criteria from another column. This is what I tried =COUNTIFS({2020.3.31 Antitrust MA Subgroup Chart Range 2},"B…Formulas and Functions244Jeffrey Metivier

#NO MATCH on simple VLOOKUPQuestionAnswered  Pending Review Neil Fleming 12 views 13 comments 0 points Most recent by Neil Fleming Formulas and FunctionsFirst post 08/06/20,Last post 08/07/20Simple VLOOKUP based on text field returns #NO MATCH. Driving me crazy!: (Image) (Image) Ideas?Formulas and Functions1213Neil Fleming

Answered  Pending Review Ishtiaq Mustafa 15 views 1 comment 0 points Most recent by [email protected] Formulas and FunctionsFirst post 08/06/20,Last post 08/06/20Hi All, I have parents and children items in my smartsheet. Some of the parent items have children under them. When I use =AVG(CHILDREN()) it gives me average % complete for each parent item. However I would like to get a total %complete …Formulas and Functions151Ishtiaq Mustafa

Answered  Pending Review Nick Rozaklis 24 views 3 comments 0 points Most recent by Nick Rozaklis Formulas and FunctionsFirst post 08/06/20,Last post 08/07/20Hello! I have a list of 38 objects (planes, trains, automobiles, what have you...) and I want to find out what the percentage complete is for either based on status. Is there a way for me to gather a total % complete based on status? For …Formulas and Functions243Nick Rozaklis

How to Match Regions to States in a ColumnAccepted AnswerAccepted Answer Donna Stuart 13 views 13 comments 0 points Most recent by Andrée Starå Formulas and FunctionsFirst post 08/06/20,Last post 08/06/20I am creating a sheet that needs to identify the "region" based on the "state" in the preceding column. I've tried multiple formulas and get back an error message (unparseable). What am I missing? =IF([State]1=ny,nj,ct,…Formulas and Functions1313Donna Stuart

Answered  Pending Review Corey Fletcher 11 views 3 comments 0 points Most recent by Corey Fletcher Formulas and FunctionsFirst post 08/06/20,Last post 08/06/20I am looking for a formula that will calculate the % of projects completed on time when the actual complete date is on or before the scheduled complete over a rolling 12month period and the Project Status is Completed. The rolling 12mont…Formulas and Functions113Corey Fletcher

Next earliest date after todayAccepted AnswerAccepted Answer Chris Carroll 15 views 3 comments 0 points Most recent by MCorbin Formulas and FunctionsFirst post 08/06/20,Last post 08/06/20Need help with a formula for this. I have a column of end dates. It should be showing this 8/12 date but I cant get it to show up. (Image)Formulas and Functions153Chris Carroll

Answered  Pending Review Sborror 13 views 2 comments 0 points Most recent by Sborror Formulas and FunctionsFirst post 08/06/20,Last post 08/06/20I'm trying to basically show sr. mgnt how many high project projects are all happening at the same time  ideally I'd like to count them  is there an easy way to do that? Essentially I'm using project type = High then want to show thos…Formulas and Functions132Sborror

IF Formula doesn't workQuestionAnswered  Pending Review Anna Pelipas 13 views 6 comments 0 points Most recent by Michael Heisler Formulas and FunctionsFirst post 08/06/20,Last post 08/07/20Please help, what's wrong? It couldn't be parsed... =IF(OR([Исполнитель (из первичной)]4 <> Исполнитель4); ([Стоимость работы (с НДФЛ) (из первичной)]4 < [Cтоимость работы (с НДФЛ)]4));"ДА";"НЕТ") Thank you in …Formulas and Functions136Anna Pelipas