Formulas and Functions
Archives for April 2020
Discussion List

Answered  Pending Review Alexa Upper 21 views 2 comments 0 points Most recent by Andrée Starå Formulas and FunctionsFirst post 04/30/20,Last post 05/01/20I have different marketing tasks where their completion dates revolve around an entire projects completion date. For example, there are tasks that need to be completed 10 days before project completion date, 20 days prior, 30 days prior, e…Formulas and Functions212Alexa Upper

Answered  Pending Review Justin Birchard 11 views 3 comments 0 points Most recent by Andrée Starå Formulas and FunctionsFirst post 04/30/20,Last post 05/14/20In this particular instance, I want the "Production" row to display in Red/Bold since it has subtasks that are past due and not ticked "completed". Is there a way to do this easily? (File)Formulas and Functions113Justin Birchard

IF Checked, Assign Auto NumberQuestionAnswered  Pending Review Randi Dean 21 views 7 comments 0 points Most recent by Andrée Starå Formulas and FunctionsFirst post 04/30/20,Last post 05/21/20Hi there! I'm no excel or Smartsheet formula wiz but I'm hoping someone here can help me figure out a way to do the following: I'd like an autopopulated number to appear in a row when the "Estimate" box is checked. There will be…Formulas and Functions217Randi Dean

Create a Duplicate FlagAccepted AnswerAccepted Answer Maria Watters 21 views 1 comment 0 points Most recent by Maria Watters Formulas and FunctionsFirst post 04/30/20,Last post 04/30/20Hello, I am trying to create a flag that turns red when a duplicate name is entered. Here is the formula I have so far, but it keeps telling me it is "#Unparseable" =IF(COUNTIF([Client/Resident Name]:[Client/Resident Name]@cell)…Formulas and Functions211Maria Watters

Answered  Pending Review MYRIAMMC 24 views 5 comments 0 points Most recent by Suveer Sadanand Formulas and FunctionsFirst post 04/30/20,Last post 05/28/20(Image) How could I covert the duration information/data in number so I can calculate variances in numbers or in percentages? I had to create a column and type each number, but I can not do that in 50 different worksheets. Is there any for…Formulas and Functions245MYRIAMMC

How can I use Smartsheet to help us with my Pitch deck?Accepted AnswerAccepted Answer Alexa Jans 11 views 5 comments 0 points Most recent by Andrée Starå Formulas and FunctionsFirst post 04/30/20,Last post 05/07/20As a radiopharmaceutical company, we have many Pitch decks in order to get grant money. Over the years, I have multiple pitch decks. Is there a way that Smartsheet could somehow help me organize those pitch decks (presentation is in powerp…Formulas and Functions115Alexa Jans

Answered  Pending Review MYRIAMMC 21 views 1 comment 0 points Most recent by Ryan Nix Formulas and FunctionsFirst post 04/30/20,Last post 05/01/20(Image) Hi there, How can the parent cell (circled in blue) automatically reflect the correct color/symbol concerning the tasks below? When I say automatically, I refer to do it by default, not me selecting the symbol all the time. Can th…Formulas and Functions211MYRIAMMC

countif by date referencesAccepted AnswerAccepted Answer Jonathan LeBlanc 75 views 3 comments 0 points Most recent by Paul Newcome Formulas and FunctionsFirst post 04/30/20,Last post 05/01/20I have a column of dates in one sheet called {Dates} In a second sheet I want to analyze the dates based on if they fall into a specific calendar month I have a column called Y (year) filled with data like 2020, and a column called M (mont…Formulas and Functions753Jonathan LeBlanc

COUNTIF HelpQuestionAnswered  Pending Review Sean Huynh 11 views 1 comment 0 points Most recent by Andrée Starå Formulas and FunctionsFirst post 04/30/20,Last post 05/01/20Hello, Is there a way to count the number of times a contact appears in a certain column, if there are multiple contacts in some of the cells? (Image) For example, I can use COUNTIF to see how many times Charles appears in cells where it i…Formulas and Functions111Sean Huynh

Help with IF(INDEX(MATCHAccepted AnswerAccepted Answer Zach Johnson 150 views 27 comments 0 points Most recent by Paul Newcome Formulas and FunctionsFirst post 04/30/20,Last post 06/10/20Hi all, First question in the forum. I'm in need of help with an INDEX(MATCH( formula that I've been using but now need to make conditional on another column. Essentially, I only want the INDEX(MATCH( formula to return a value when a col…Formulas and Functions15027Zach Johnson

Calculate DowntimeQuestionAnswered  Pending Review Darla Brown 1 view 3 comments 0 points Most recent by Sean Morgan Formulas and FunctionsFirst post 04/30/20,Last post 05/02/20Hi everyone! Hope you are safe and healthy! I'm needing a formula to calculate downtime for below. Any takers? (Image)Formulas and Functions13Darla Brown

How to convert Date (MM/DD/YYYY) into Month and Quarters and Year such as "Q1 2020" and "Jan 2020"Accepted AnswerAccepted Answer Anil Rattan 33 views 2 comments 0 points Most recent by Anil Rattan Formulas and FunctionsFirst post 04/30/20,Last post 05/04/20How to convert Date (MM/DD/YYYY) into Month and Quarters and Year such as "Q1 2020" and "Jan 2020"Formulas and Functions332Anil Rattan

Answered  Pending Review Nichole Pyle 21 views 4 comments 0 points Most recent by Jon Baier Formulas and FunctionsFirst post 04/30/20,Last post 05/01/20Hello, I can't seem to figure out how to get a count of all items with a start date between 5/4/20 and 5/8/20. This will be used as part of a COUNTIFS formula if that helps, I was able to get the first two criteria in but can't figure out…Formulas and Functions214Nichole Pyle

rollup formulasAccepted AnswerAccepted Answer steve50951 11 views 2 comments 0 points Most recent by steve50951 Formulas and FunctionsFirst post 04/30/20,Last post 04/30/20I have personnel sending out emails. I want to track how many total emails each person has sent in a given city they put a checkbox after each email has sent. I would like a formula that would total the checked boxes from each person. is t…Formulas and Functions112steve50951

Answered  Pending Review teresa.hron92846 31 views 1 comment 0 points Most recent by Joanna Waligora Formulas and FunctionsFirst post 04/30/20,Last post 05/01/20https://app.smartsheet.com/sheets/2rRMWQV6RH42W7mhC7P9FwRW3p288vVR65PjcxC1?view=gantt I would like to summarize the planned and completed work using the following fields and group by SubType. Planned is anything <> Backlog or Group …Formulas and Functions311teresa.hron92846

Multiple IF/AND for duration and statusAccepted AnswerAccepted Answer Alessandro Terranova 21 views 2 comments 0 points Most recent by Alessandro Terranova Formulas and FunctionsFirst post 04/30/20,Last post 05/01/20Hi Community, I am trying to structure an IF/AND formula to reflect the following conditions: IF [Duration]@row <> 0, AND IF [Complete]@row =0, "Not Started" IF [Duration]@row <> 0, AND IF [Complete]@row =0, AND IF[Co…Formulas and Functions212Alessandro Terranova

Answered  Pending Review John M Lee 32 views 1 comment 0 points Most recent by Paul Newcome Formulas and FunctionsFirst post 04/30/20,Last post 05/01/20We have a reference sheet which has a first column of Client ID followed by columns for Analyst Email and Manager Email (both are Contact List column type). On another sheet we have multiple columns and are trying to populate both the Ana…Formulas and Functions321John M Lee

Answered  Pending Review Randy Willey 31 views 1 comment 0 points Most recent by Andrée Starå Formulas and FunctionsFirst post 04/30/20,Last post 05/01/20I have a main sheet containing a list of projects with the first column containing the ID of the project which is configured as an autonumber column. I am working on setting up another sheet to track projects tasks and want to be able to …Formulas and Functions311Randy Willey

Answered  Pending Review Danielle Arteaga 43 views 4 comments 0 points Most recent by Andrée Starå Formulas and FunctionsFirst post 04/30/20,Last post 05/05/20I have two text columns. One column contains titles of documents. The second column contains specifies the document's "category." One of the categories in the "Category" column is "Presentations." However, in …Formulas and Functions434Danielle Arteaga

Answered  Pending Review Jana Brits 42 views 3 comments 0 points Most recent by Andrée Starå Formulas and FunctionsFirst post 04/30/20,Last post 05/14/20I have a sheet with an automatic numbering column. Data for this sheet will be captured through a webform. I need certain columns, which will not be populated throught the webform, to have formulas in them. How do I add those formulas with…Formulas and Functions423Jana Brits

Answered  Pending Review Alessandro Terranova 41 views 3 comments 0 points Most recent by Andrée Starå Formulas and FunctionsFirst post 04/30/20,Last post 05/14/20Hi Community, I have a master Intake sheet fed by an intake form, this serves the purposes of multiple departments. I would like to create a second sheet that automatically receives data from the primary intake sheet that only captures th…Formulas and Functions413Alessandro Terranova

ISNA Excel FormulaAccepted AnswerAccepted Answer Jen Evans 21 views 3 comments 0 points Most recent by Paul Newcome Formulas and FunctionsFirst post 04/30/20,Last post 05/01/20I am trying to recreate an excel formula in Smartsheet that looks for the column Header word in the proceeding columns within that same row. If it finds the word in that row, it then places the word in the cell that the formula exists. So…Formulas and Functions213Jen Evans

Answered  Pending Review Tara M 51 views 2 comments 0 points Most recent by Tara M Formulas and FunctionsFirst post 04/30/20,Last post 05/04/20Hi, I am trying to track the number of times a call occurs within a date range in a cross sheet reference formula. The user types the date on the call log and I would like to use the dates as a way to measure how many times calls occur wi…Formulas and Functions512Tara M

Need per user formulas for counting cellsAccepted AnswerAccepted Answer Clark Everson 1 view 4 comments 0 points Most recent by Jon Baier Formulas and FunctionsFirst post 04/30/20,Last post 04/30/20I am trying to create 2 formulas to get information for a dashboard. The first is a formula that looks at RYGB as well as assigned user and counts all cells if they match both criteria. I tried this but can't seem to get it to work: =IF([…Formulas and Functions14Clark Everson

If then formulasQuestionAnswered  Pending Review Tammy Parker 21 views 2 comments 0 points Most recent by Paul Newcome Formulas and FunctionsFirst post 04/30/20,Last post 05/01/20I am trying to create my own easy work around to capture the date when something is completed. IF the Completed Box is checked AND I have a last modified date column Can I create a date completed box and say IF the Completed Box is checke…Formulas and Functions212Tammy Parker

How to update a formula to automatically populate for the prior Monday to a cell dateAccepted AnswerAccepted Answer KatieFields 21 views 4 comments 0 points Most recent by KatieFields Formulas and FunctionsFirst post 04/30/20,Last post 04/30/20Hi! I have the following formula from the discussion board (shoutout to Craig) for automatically populating the Monday prior to a certain date: =IF(ISDATE([email protected]), IF(WEEKDAY([email protected]) = 1, [email protected]  6, [email protected]  WEEKDAY([email protected]) + 2)…Formulas and Functions214KatieFields

Answered  Pending Review Emma Schachter 32 views 8 comments 0 points Most recent by Paul Newcome Formulas and FunctionsFirst post 04/30/20,Last post 05/04/20Hello! I am working on a sheet with Start/Finish dates and need to populate status' into the following 4 categories: not started, in progress, complete, up next For not started/in progress/complete i have the following formula working: …Formulas and Functions328Emma Schachter

COUNTIFS FormulaAccepted AnswerAccepted Answer Lynn Meadow 11 views 3 comments 0 points Most recent by Andrée Starå Formulas and FunctionsFirst post 04/30/20,Last post 04/30/20I am fairly new to working with formulas. I have a Project Status column that contains several values with "In Progress". For example, In Progress  Scoping, In Progress  Development, etc. I would like to count all the instances…Formulas and Functions113Lynn Meadow

Formula Not Working. Turning Sizes into NumbersAccepted AnswerAccepted Answer Marcel Hovsepian 1 view 3 comments 0 points Most recent by Andrée Starå Formulas and FunctionsFirst post 04/30/20,Last post 05/06/20Hello, I am trying to write a simple formula where I translate a size value into a number. For example in my column called "Size": I want to create a formula that turns the following size into corresponding numbers in another co…Formulas and Functions13Marcel Hovsepian

Function to retrieve count in a multiselect columnAccepted AnswerAccepted Answer Shafi M 32 views 7 comments 0 points Most recent by Paul Newcome Formulas and FunctionsFirst post 04/29/20,Last post 04/30/20Hi, So I have a multiselect column which upto 7 selections currently. What I am trying to do is have a bar chart of count based on the number of selections in each cell. To give an example. row 1 : A, B, C,D,E,F,G row 2: B,C,D,E row 3: A…Formulas and Functions327Shafi M