Formulas and Functions
Archives for May 2020
Discussion List
-
Need help with an "IF", "CONTAINS" and "OR" formulaAccepted AnswerFirst post 05/31/20,Last post 06/01/20Hello everyone First, thanks for your help :) I'm trying to make a formula. I have a cell (cell#1) that has 4 values in it (" one", "two", "three", "four"). I'm looking for a formula that would wor…Formulas and Functions212TReid -
Display date fields from two different sheetsAccepted AnswerAccepted Answer Jeana 31 views 10 comments 0 points Most recent by Paul Newcome Formulas and FunctionsFirst post 05/31/20,Last post 06/03/20Hi, I'm tracking an estimated Start and End date (in a Project Sheet) and an Actual Start and End date (in a tracking sheet). In both sheets the rows where the dates are entered are CHILDREN and they both have an Asset # that I'm can match…Formulas and Functions3110Jeana
-
IF(OR Statement giving #unparseable, using ; or , ?Accepted AnswerAccepted Answer Lisa Rogers 21 views 2 comments 0 points Most recent by Genevieve P Formulas and FunctionsFirst post 05/30/20,Last post 06/03/20Please help. I keep getting #unparseable error for the following formula: =IF(OR([DLExpiration Date]1 > TODAY(); [MECExpiration Date]1 > TODAY()); "NON COMPLIANT"; "COMPLIANT") I want the cell in my Status colu…Formulas and Functions212Lisa Rogers
-
I get an #INVALID OPERATION using SUMIFS with multiple criteria referencing a separate sheet.Accepted AnswerAccepted Answer Devin Barta 22 views 3 comments 0 points Most recent by Genevieve P Formulas and FunctionsFirst post 05/30/20,Last post 06/01/20I am having trouble getting this formula right. I want to pull the qty of flooring scheduled to run in a given week (1-52) provided it requires one of 2 types of veneer. (Acacia 1220x077x1.5 or Acacia 1220x185x1.5) (Image) =SUMIFS({Schedu…Formulas and Functions223Devin Barta
-
How do I show health based on end date & % complete?Accepted AnswerAccepted Answer Michelle Vidal 22 views 2 comments 0 points Most recent by Michelle Vidal Formulas and FunctionsFirst post 05/29/20,Last post 05/30/20I'm trying to show health of a project based on how far out from End date + how much of the work has been completed. Green: 100% complete before or on End date Yellow 70-99% and today is < 1 range of End Date <69% and date within 2 …Formulas and Functions222Michelle Vidal
-
Workaround for broken UTC System Created Date ColumnAccepted AnswerAccepted Answer ScionoftheNight 42 views 6 comments 0 points Most recent by Werner Gerstacker Formulas and FunctionsFirst post 05/29/20,Last post 05/29/20Hi, my problem is the same as in https://community.smartsheet.com/discussion/19461/wrong-date-showing-seems-to-be-4-hours-off-the-difference-between-gmt-and-my-time-zone In short, I am on GMT-7 time, while Smartsheet operates under UTC. B…Formulas and Functions426ScionoftheNight -
How do you create a Sum if equation when the criteria is based on a value in a Multi-Select ColumnQuestionAnswered - Pending Review Andrea Hill 12 views 2 comments 0 points Most recent by Andrea Hill Formulas and FunctionsFirst post 05/29/20,Last post 06/01/20When I chose just one of the selections (I have been using "Product" no" = "or other signs) it only sums the values were there is only "Product" . the equation wont sum if there are other selections included.Formulas and Functions122Andrea Hill
-
Answered - Pending Review Carolina Rivera 14 views 1 comment 0 points Most recent by Genevieve P Formulas and FunctionsFirst post 05/29/20,Last post 06/03/20Hi! So I have two sets of data that I would like to graph using a stacked chart. Data set 1 contains a number of actions that are divided by month, for example, the month of May has 5 actions and the month of June has 3. Data set 2 contain…Formulas and Functions141Carolina Rivera -
Answered - Pending Review Samuel Dowdy Jr 21 views 1 comment 0 points Most recent by Sean Morgan Formulas and FunctionsFirst post 05/29/20,Last post 05/30/20Sheet --> https://app.smartsheet.com/sheets/Rp5CHm6mFGcw2CG7x8rFjmfGwVpG394xffJw2x91 I really need some help placing data throughout this sheet by using formulas. I know what I want but I dont know the formulas. I want the total mont…Formulas and Functions211Samuel Dowdy Jr
-
Answered - Pending Review courtneyjenkins8 21 views 9 comments 0 points Most recent by Andrée Starå Formulas and FunctionsFirst post 05/29/20,Last post 06/02/20When "Connect Done" and "Core/Micro Done" are both checked, I want "Client Done" to automatically be checked. Can someone help me with the formula? I'm guessing its an =IF (Image)Formulas and Functions219courtneyjenkins8
-
Answered - Pending Review Jeana 21 views 5 comments 0 points Most recent by Paul Newcome Formulas and FunctionsFirst post 05/29/20,Last post 06/01/20So I have a drop down column with options from 1--3. Each has a description similar to this: Priority 1 - Nice to have 2 - Needed 3 - Required I would like to total the actual number that is selected in a row below these options (then I'l…Formulas and Functions215Jeana
-
Stacked Bar GraphQuestionAnswered - Pending Review katebrew 12 views 3 comments 0 points Most recent by Werner Gerstacker Formulas and FunctionsFirst post 05/29/20,Last post 05/29/20I am trying to create a stacked bar graph using data from a sheet summary report however I have not get the report data to pull into rows as I select the field to be added to the report they are popluated as columns only.Formulas and Functions123katebrew
-
Answered - Pending Review Ashley Pierce 23 views 5 comments 0 points Most recent by Paul Newcome Formulas and FunctionsFirst post 05/29/20,Last post 06/01/20(Image) For example, for the DFM/DDFM Support Type, I would like to know that there are: Communication: (1) Ashley Pierce (1) Tanner May Proposal Review: (1) Alexa Briggs (2) Ashley PierceFormulas and Functions235Ashley Pierce
-
Answered - Pending Review Lng98881 11 views 2 comments 0 points Most recent by Werner Gerstacker Formulas and FunctionsFirst post 05/29/20,Last post 06/04/20Hello, I have a Smartsheet where I'm trying to count distinct values in a column. Currently I've tried this but get #UNPARSEABLE. =Count(Distinct(Collect(Prj_ID:Prj_ID,Prj_ID:Prj_ID,[email protected]))) Where Prj_ID is the column where I want to…Formulas and Functions112Lng98881
-
change name from @cell to email in another @cellAccepted AnswerAccepted Answer Jörg Schmidt Novum Hospitality 11 views 1 comment 0 points Most recent by Werner Gerstacker Formulas and FunctionsFirst post 05/29/20,Last post 05/29/20Hi, what could be the Formula in @cell "b" to insert the email address of a person whose name is written in cell "a" ?Formulas and Functions111Jörg Schmidt Novum Hospitality
-
Locking a line after a certain dateAccepted AnswerAccepted Answer Julie Barbeau 13 views 1 comment 0 points Most recent by Paul Newcome Formulas and FunctionsFirst post 05/29/20,Last post 05/29/20Hi to all, Does he have a way of using a formula to make a line lock after a certain date? For example, in our timesheet, create a formula that says: lock this row if the date in the column ''Date'' is 7 days after today's date? ThanksFormulas and Functions131Julie Barbeau
-
How can I use SUMIFS (or another function) to sum values if two rows match each other?Accepted AnswerAccepted Answer Amy Balich 31 views 5 comments 0 points Most recent by Paul Newcome Formulas and FunctionsFirst post 05/29/20,Last post 06/01/20(Image) I am trying to add together the total quantity made on a certain date and time. For example, in the screenshot, I have two rows that contain 5/18/20 that occur on floorhour 5. Is there a formula I can write to add together the valu…Formulas and Functions315Amy Balich -
COUNTIFS NON Blank QuestionAccepted AnswerAccepted Answer Joel Brumback 41 views 3 comments 0 points Most recent by Paul Newcome Formulas and FunctionsFirst post 05/29/20,Last post 05/29/20I have two columns, "Assigned To" and "Date Entered". What I want is to count blank "Date Entered" cells that are assigned to a specific person. Is this possible?Formulas and Functions413Joel Brumback
-
#Invalid Data Type HelpQuestionAnswered - Pending Review Stefano Ferretti 21 views 2 comments 0 points Most recent by Andrée Starå Formulas and FunctionsFirst post 05/29/20,Last post 06/01/20Hi All, I have this simple functions: =IF(COUNTIF(A1:D1,"apple"),"YES","NO") The formula works fine if in the range A1:D1 I have 1 cell containing "apple". If 2 or more cells contain "apple&qu…Formulas and Functions212Stefano Ferretti
-
Accepted Answer Benjo09 11 views 3 comments 0 points Most recent by Andrée Starå Formulas and FunctionsFirst post 05/29/20,Last post 05/29/20Hi, I have a grid or sheet where the rows which document status is already "SPC-approved" need to copy or populate to another sheet called "Approved Docs". Is there a way where this Approved-Docs sheet can automaticall…Formulas and Functions113Benjo09
-
JOIN formula to ignore cells in blankAccepted AnswerAccepted Answer Jean Kim 33 views 5 comments 0 points Most recent by Andrée Starå Formulas and FunctionsFirst post 05/29/20,Last post 05/29/20Hi Trying to get below formula work. =JOIN([email protected]:[email protected], [email protected]:[email protected], NOT(ISBLANK(@cell), "| ")) Expected results are on the lat column. Formula above is returning "Incorrect argument set" error. What a…Formulas and Functions335Jean Kim
-
vlookup with dateQuestionAnswered - Pending Review Archie Villa 55 views 1 comment 0 points Most recent by Andrée Starå Formulas and FunctionsFirst post 05/29/20,Last post 05/29/20Hi everyone I am currently doing a vlookup to a reference column with a date format, to another sheet in a specific cell but my column is in text/number format. i got this error "invalid column value". Is there a way i could con…Formulas and Functions551Archie Villa
-
Answered - Pending Review bthorn 42 views 1 comment 0 points Most recent by Andrée Starå Formulas and FunctionsFirst post 05/29/20,Last post 05/29/20Hi! I'm managing a retrofit project and the end goal is to create an executive dashboard highlighting key metrics in the form of charts and/or graphs. From what I've found via searches, it seems like I need to utilize the Summary function…Formulas and Functions421bthorn -
Answered - Pending Review Richard Heath 13 views 1 comment 0 points Most recent by Andrée Starå Formulas and FunctionsFirst post 05/29/20,Last post 05/29/20Hello, I've developed a sheet that our Project Manager use to generate a Certificate based on a number of criteria. So the Certifcate (a dashboard) pulls the correct data from multiple sources, the user needs to simply select "YES&quo…Formulas and Functions131Richard Heath
-
Answered - Pending Review Vanessa Campos 31 views 1 comment 0 points Most recent by Andrée Starå Formulas and FunctionsFirst post 05/29/20,Last post 05/29/20I have a list of deliverables with a % complete column and I want to calculate the total number of: completed (at 100%) in progress (between 1% and 99%) not started (0%) I am using the following formula for completed and it is giving …Formulas and Functions311Vanessa Campos
-
First post 05/28/20,Last post 06/01/20So apparently smartsheet created and modified columns retain time down to at least the seconds, I wasn't willing to keep checking beyond that. I utilized this to report the time difference in form submissions down to the seconds. Just shar…Formulas and Functions318[email protected]
-
SUMIFS with Multiple CriteriaQuestionAnswered - Pending Review Fiona Lee 42 views 1 comment 0 points Most recent by Mike Wilday Formulas and FunctionsFirst post 05/28/20,Last post 05/29/20Hello! My ultimate goal is to be able to get a sum of the inventory per item, by month. For example, I would like a formula to return the amount of Control Panels there are in June 2020. Based on the screenshot below, the result I should r…Formulas and Functions421Fiona Lee
-
Accepted Answer Jeana 47 views 5 comments 0 points Most recent by Andrée Starå Formulas and FunctionsFirst post 05/28/20,Last post 05/29/20I want to populate a name(s) in a Contact list based on the value of another field. For example: If a cell contains "Copyediting" I want Holly's name to be added to Additional Assets (a contact list). Thanks, JeanaFormulas and Functions475Jeana
-
Formula: Count symbols in an RYG columnAccepted AnswerAccepted Answer Scott Peloquin 51 views 5 comments 0 points Most recent by Mike Wilday Formulas and FunctionsFirst post 05/28/20,Last post 06/19/20I am trying to use a simple formula to count my number of different colored symbols in a column, RYG. I have many parent rows with 6 children each. 14 of the parent rows have a green symbol and all the others are currently blank, no symb…Formulas and Functions515Scott Peloquin
-
how to get ° degrees circle symbol in a cellAccepted AnswerAccepted Answer Lisa_Doris 21 views 4 comments 0 points Most recent by Paul Newcome Formulas and FunctionsFirst post 05/28/20,Last post 06/01/20How do I write the degree symbol and is there a sheet with all symbol shortcuts available somewhere, not just the most popular?Formulas and Functions214Lisa_Doris
