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
Discussion List

Text change based on value of numberAccepted AnswerAccepted Answer Kelly Buse 12 views 4 comments 0 points Most recent by Andrée Starå Using SmartsheetFirst post 01/22/20,Last post 01/23/20Hi All, I was hoping to create a formula that changes the text of a cell based on the number in another cell. Here is what I'm trying to make the cell display at each value range: Value is under or equal to 20  Cell to display "N/A…Using Smartsheet124Kelly Buse

Question Edgar Alvarado García 7 views 0 comments 0 points Started by Edgar Alvarado García Formulas and FunctionsFirst post 01/23/20,Last post 01/23/20Hi there, In a sheet I've few cells that have formulas, columns were those cells are aren't lock, however the users cannot overwrite the formulas, any idea why it may happen? The idea was to create formulas as a guidance but editors shoul…Formulas and Functions70Edgar Alvarado García

First post 01/23/20,Last post 01/23/20Hello, I need a formula that can help me calculate the sum within a column based on date. Not sure if this can be achieved. We are updating information on a weekly basis and I have different rows for the same date. I need a formula to give…Formulas and Functions103Jona Gjylameti

averageif with multiple criteriaAccepted AnswerAccepted Answer taylor.thompson89836 13 views 2 comments 0 points Most recent by taylor.thompson89836 Formulas and FunctionsFirst post 01/23/20,Last post 01/23/20I am trying to find the average of time in position (demographics range 1)when the job class (demographics range 3) is manager or higher. I'm currently using =avg(collect to try to specify the multiple criteria but am getting a #divide by …Formulas and Functions132taylor.thompson89836

Formula in % Allocation Column?UnansweredFirst post 01/23/20,Last post 01/23/20I am certain this has been asked either currently or in the past, but is the capability to include a formula in the % Allocation column on the product roadmap? Right now, I have a column entitled "% Alloc Calc" next to the "…Formulas and Functions40Guy Behanna

First post 08/28/19,Last post 01/23/20Sheet 1: Work Scheduled (number of work units scheduled in a given month) Sheet 2: Budgeted Cost of Work Scheduled (to determine the total cost of the work scheduled in a given month) I would like to multiply a cell from Sheet 2 with the…Formulas and Functions24116JoeS

Automatic ticket numbering by locationAccepted AnswerAccepted Answer M. David 28 views 11 comments 0 points Most recent by Paul Newcome Formulas and FunctionsFirst post 01/22/20,Last post 01/23/20Hello. I worked with someone on a previous discussion, but for some reason cannot locate it now. I have one data base file with the work orders from many different community locations. I wanted a solution for the Work Order Number to be a …Formulas and Functions2811M. David

Answered Sean Taber 153 views 18 comments 0 points Most recent by Genevieve P. Formulas and FunctionsFirst post 06/18/18,Last post 01/23/20Hello, I'm trying to calculate the average number of days but only for columns that meet the criteria. I have a formula that is calculating the networkdays between two dates. This column is called Implementation Time (Days)  BD. I am no…Formulas and Functions15318Sean Taber

Need assistance with a formulaAnsweredAnswered Nikhil Chawla 14 views 1 comment 0 points Most recent by [email protected] Formulas and FunctionsFirst post 01/23/20,Last post 01/23/20Hello, I am using the below formula to count the number of rows where 140R was checked previous month. This formula was working perfectly fine until this month when the year changed. How can I fix this formula where previous month should b…Formulas and Functions141Nikhil Chawla

Formula to average a range in another sheet corresponding to cells that contain parts of textAccepted AnswerAccepted Answer lhumphrey 9 views 3 comments 0 points Most recent by [email protected] Formulas and FunctionsFirst post 01/23/20,Last post 01/23/20I can't seem to beat this formula. I've tried COUNTIF, COLLECT, CONTAINS, and when I'm not getting an error I'm getting 0. I want to see the average of successful deliveries below for all Up to Date emails in Q1. In the sheet I'm working w…Formulas and Functions93lhumphrey

Sheet Summary Formula for Due DatesUnansweredFirst post 01/23/20,Last post 01/23/20Hello, I have two separate column Status and Due Date and I'm looking to create a formula on the sheet summary for the amount of items that are due today and past due. Is there a way to create one?Formulas and Functions60Heidi Ng

First post 01/22/20,Last post 01/23/20Hello, I'm trying to calculate average work days that each command takes to return a request. However, it has to meet several other stipulations. "Type of Request" has to include "FOIA" (dropdown column multiselect) …Formulas and Functions195fennerb

First post 12/20/18,Last post 01/23/20I have a sheet with a list of assets. There is a pull down menu that defines the status  I need to know when the menu is set to "complete". I can do that easily with a filter  but I want to cull the entries before a specific da…Formulas and Functions888CamCrichton

Answered Kelly Buse 40 views 11 comments 0 points Most recent by Paul Newcome Formulas and FunctionsFirst post 12/18/19,Last post 01/23/20Hello, Another request for information! I have a parent status ball that I would like to change color based on the 2 deadline status balls and the dominant completion ball (i.e. row 1 & row 2  then row 3 being the dominant ball). The…Formulas and Functions4011Kelly Buse

First post 01/17/20,Last post 01/23/20Hi team, I'm using Placeholders in a workflow to generate individual emails for an update request. Although the same individual received the update, they will forward as needed, so I need to ensure each update request for the day doesn't …Formulas and Functions319Rob Kozak

Fill across not working as expected?UnansweredFirst post 01/22/20,Last post 01/23/20Wondering if anybody else is experiencing this  I have performed this scenario hundreds of times without issue so wondering what's up! I have a formula based on full Column:Column ranges. The criteria ranges are set with $, the range to s…Formulas and Functions250Jaye Tatone

First post 01/23/20,Last post 01/23/20The column name is [Task] and a cell value called "Project Details" is located somewhere in the column. Their children is project 1, project2, ... etc.... I want to count the number of children under "Project Details". …Formulas and Functions81Chuen

Flag column based on date & other criteriaAccepted AnswerAccepted Answer [email protected] Seattle 13 views 3 comments 0 points Most recent by Andrée Starå Formulas and FunctionsFirst post 01/22/20,Last post 01/23/20I'm trying to figure out how to set up an alert to automatically notify someone if the entry row hasn't met criteria (checkbox) within 5 days. I was thinking about creating a flag column that kicks off an email alert but I can't figure out…Formulas and Functions133[email protected] Seattle

Accepted Answer Justin Runner 38 views 7 comments 0 points Most recent by Andrée Starå Formulas and FunctionsFirst post 01/21/20,Last post 01/23/20I'm looking for a formula to help me calculate the length of hours an individual has worked. Employees enter their "Haul Start Time" and "Haul End Time" manually by picking from a drop down list of times in 24hr format.…Formulas and Functions387Justin Runner

First post 05/17/19,Last post 01/23/20Hey Is it possible to have the the dropdown list of a column changed based on the value of another column? Eg. If column A's value is set to "Work stage 2" column B's dropdown list will have a specific list, but when it is se…Formulas and Functions29916[email protected]

SUM totals of VLOOKUPAccepted AnswerAccepted Answer @SPark 19 views 3 comments 0 points Most recent by Paul Newcome Formulas and FunctionsFirst post 01/22/20,Last post 01/22/20Looking for help on a formula. I need the Forecast Adjustment column on (A) to add all amounts pulling (B). These should total $13,416.67, however, it it showing as $5,416.67. Please help! How do I change the formula to sum the VLOOKUPS? …Formulas and Functions193@SPark

Help with an IF/AND/OR formulaAccepted AnswerAccepted Answer Jenna Corso 25 views 3 comments 0 points Most recent by Paul Newcome Formulas and FunctionsFirst post 01/22/20,Last post 01/22/20Hello, I am trying to write a formula to return today's date when all of the following are met: Column 1 is not blank Column 2 is not blank Column 3 is either Kangaroo or Platypus If any of these is untrue, then I would like the cell to…Formulas and Functions253Jenna Corso

First post 01/20/20,Last post 01/22/20I work with large data sets on parts (60,000 SKUs) and I have a form and workflow for processing special pricing requests from our field. I'm curious if I can house the prices on an external excel file or sharepoint and pull in the pricing…Using Smartsheet251Nolan Schmidt

Nest if/or formulaAnsweredAnswered raymond.j.riosiii94806 19 views 1 comment 0 points Most recent by Paul Newcome Formulas and FunctionsFirst post 01/22/20,Last post 01/22/20Hi Community, I need your help... I'm trying to create a formula that captures "No Action Taken". The individual IF Statements are as follows: If [Flu Vaccination Date] is "Blank" OR If [Flu Vaccination Date] is "…Formulas and Functions191raymond.j.riosiii94806

First post 09/18/19,Last post 01/22/20I’m working on a SUMIFS formula to add/sum numbers in 2 or more separate cells based on a checkbox input in 2 or more other cells. I’m able to do so only if the number columns and the checkbox columns are next to another. Is there a way …Formulas and Functions174Sam Gold

First post 11/01/19,Last post 01/22/20I'm new to this, and I'm having issues with counting the number of projects where Project Type = X and Project Status does Not equal Complete or Cancelled. I'm fumbling my way through unsuccessfully. This will go on a dashboard where I hav…Formulas and Functions337Brian Lynch

Answered Shawn Borror 27 views 2 comments 0 points Most recent by Genevieve P. Formulas and FunctionsFirst post 01/20/20,Last post 01/22/20I can't figure out how to do this formula and I've been searching for it in community. I've tried the following: =COUNTIFS(Portfolio Rollups Range 1, "Corp IT",AND({Portfolio Rollups Range 8},="In Progress",{Portfolio …Formulas and Functions272Shawn Borror

First post 01/22/20,Last post 01/22/20I've built a Smartsheet (with some help from the ProDesk) that assigns a task to a contact based on the date/time of a request. The sheet looks at the date stamp on the request then references another sheet (using WEEKNUMBER and WEEKDAY) …Formulas and Functions100Michael Palko

First post 01/20/20,Last post 01/22/20I have my cashflow forecast on Smartsheet, and i have a formula which looks at the balance on the row above to apply the balance for the current row as follows =BALANCE1350 + [in's]1351  [out's]@row. When i shift the row up or down within…Formulas and Functions253AbbyT

Register and calendar integrationAccepted AnswerFirst post 01/05/20,Last post 01/22/20Hi, I need assistance in creating a calendar which will show the number of locations where material deliveries will happen (5 locations) over 24 hours  365 days a year. The idea is to then create a dashboard with the form and calendar an…Formulas and Functions4720sahilhq