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 and Functions
Discussion List
-
First post 01/22/20,Last post 01/23/20Hello, I am trying to count the amount of times a particular "ticket category" is selected. Some of our tickets have multiple values to be applied and I am noticing that my formula does not count an item if there was more than …Formulas and Functions112Daniel Gomez -
First 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.thompson89836First 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
-
First post 01/23/20,Last post 01/23/20Help! I cannot figure out a formula for SUMIF or SUMIFS to add a range of numbers in a column that are greater than 25,000 based on a checkbox. =SUMIFS({Content Limit - NB}, >25000, {Reporting - NB}, 1) <-- why doesn't this work. heh…Formulas and Functions30@Diana -
Combining formulasAccepted AnswerFirst post 01/23/20,Last post 01/23/20Hi, I need some help with a formula. I have a one check mark column that I would like to 'check' if any of the following formulas are true. Can I combine them into one large formula? =IF(AND([Key Activity]@row, 1, [CEC Tech]@row, 1), 1) …Formulas and Functions102Tina
-
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 01/17/20,Last post 01/23/20=IF(Status6 = "Open", IF([Due Date]6 <= TODAY(2), 1, 0), (IF([Send to High Risk Report Now]6 = 1, 1, 0))) https://us.v-cdn.net/6031209/uploads/777/529C59A6UPMV.png I have this formula that each IF function works on its own, b…Formulas and Functions285Angela Ryer
-
Using Emojis in SmartsheetsUnansweredFirst post 01/23/20,Last post 01/23/20We want to use icons that relate to our work. I have an IF formula that includes emojis. =IF(Classification4 = "BAGS", "🎒", IF(Classification4 = "SOCKS", "🧦", IF(Classification4 = "GLOVES"…Formulas and Functions70mel.greenspan -
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 AnswerFirst 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
-
First 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 formulaAnsweredFirst 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
-
First 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
-
First post 01/22/20,Last post 01/23/20Hello, Looking for formula help. I have a master Smartsheet with email addresses and a second Smartsheet with a form that will populate with email addresses entered by the user, so they may not be entered 100% correctly (fuzzy lookup?). I…Formulas and Functions161Dedrick Tai
-
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
-
Help with Design/Nested FormulasAnsweredFirst post 01/22/20,Last post 01/23/20So I need some help with my design or nested formulas..... I have a formula in a column (listed below) which is perfect, except that in a few instances there are exceptions, and I must add up several different $ amounts from a few CLINS on…Formulas and Functions225ferguson_pamela107206
-
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" (drop-down column multi-select) …Formulas and Functions195fennerb
-
Automation with Time selectionAccepted AnswerFirst post 01/22/20,Last post 01/23/20Is there a way to allow a user without access to the smartsheet to select a time and date? We use the automation to send emails out to contractors so that they can give us a work start date. However, we also want to know the exact time of…Formulas and Functions133Sitlaly Avelino
-
Currency Conversion USD to CADAccepted AnswerFirst post 01/23/20,Last post 01/23/20Hello I am currently using both currencies in my sheet. I created a column drop down, need help with a formula. If I select USD from that column, the amount should be converted to CAD if I select CAD from that drop down column then the am…Formulas and Functions82PRITIKA SAINI
-
First post 01/22/20,Last post 01/23/20I am trying to have some date fields auto-populate based on some restrictions. Date Columns 2, 3 and 4 would be dependent on Date column 1. For example: An Inspection must always occur at least 2 days after Work Start. If I enter Work Star…Formulas and Functions195Brigette Lepe
-
How do I calculate a Group Average for the Most Recent Date?Accepted AnswerFirst post 01/22/20,Last post 01/23/20I have assessment scores for individuals within different groups (Male or Female). The assessments are taken over time as a sort of "pulse check." Now, I want to be able show a group average (average for all Males and average for…Formulas and Functions203Scott Anderson
-
At Risk with % Complete & Dates FormulaAccepted AnswerFirst post 01/22/20,Last post 01/23/20Oh formulas. . . I feel like I am close, but could use some help. I would like the 'risk' flag to be marked if the data meets the following criteria: % complete is equal to or less than 50% AND The date is within 3 days; Today; or is in t…Formulas and Functions213lcymr13
-
First post 01/23/20,Last post 01/23/20All, (Please see screenshot for reference) I am trying to get a "total locations" excluding if "cancelled" is checked. Current formula in "total locations is. =COUNT([Location #]:[Location #]) This gives the total…Formulas and Functions40Beth Faircloth
-
First post 01/22/20,Last post 01/23/20for exemple https://us.v-cdn.net/6031209/uploads/220/GBHD6PIX1YDQ.png In my sheet https://us.v-cdn.net/6031209/uploads/059/3CGRABEL8NHU.png i would like in my reponse here https://us.v-cdn.net/6031209/uploads/934/GFSNDQEV6AU6.png corre…Formulas and Functions161Laurence Bergeron -
I need help with nesting syntax pleaseAccepted AnswerFirst post 01/23/20,Last post 01/23/20I am trying to count the number of tasks assigned to a resource, let's say "Jane". The COUNTIF function works if there is only one resource in the "Assigned To" category, but I also need to include the number of times …Formulas and Functions155Lisa Kastelic
-
SUMIF based on checkbox columnAnsweredFirst post 01/22/20,Last post 01/23/20This discussion was created from comments split from: How is the checkbox function recognised?.Formulas and Functions144System -
First post 01/17/20,Last post 01/23/20Hello, I actually have some #NOMATCH problem, that I can't explain because it work different cells of my sheet that have same parameters. My formula is : =IF(ISBOOLEAN(VLOOKUP([N° CONTAINER]1; {LDS Container Dashboard plage 2}; 21)) = 1; 1…Formulas and Functions237Laurent DESSOLLES
-
First post 01/23/20,Last post 01/23/20Hello, I have 2019 monthly data in Sheet 1 and would like for it to be populated in Sheet 2 based on current 2020 month. Basically, I want to pull same month last year data from sheet 1 to sheet 2. I've tried using VLOOKUP in sheet 2 cell …Formulas and Functions120Krishna Patel
-
IF(INDEX(MATCH Function - For Resource TrackingAccepted AnswerFirst post 01/23/20,Last post 01/23/20Hello, I have an Procurement Engagement Form (PEF) and a Resource Tracker. I'm trying to pull the Contract End Date into the Resource Tracker using the Function below. Its working perfectly in Excel, although unfortunately I'm struggling…Formulas and Functions126davidmartin95826
