Formulas and Functions
Archives for March 2018
Discussion List

First post 03/22/18,Last post 03/23/18I am working on a budget sheet, and need to be able to calculate OPEX expenses per site, and CAPEX expenses per site. I've got 6 different sites, and need these totals for each quarter. The formula below seems to work, but I can't use it…Formulas and Functions111Jennifer M

First post 03/22/18,Last post 03/23/18I have a formula =COUNTIF(Age8, >65) which is working, but I want it to only give me a count if a certain checkbox field is checked. Is there a formula for this?Formulas and Functions11tcanelli41471

First post 03/22/18,Last post 03/26/18Is there a way to set a formula so all text in the column or sheet is upper case even if someone enters it on a form in lower or title case?Formulas and Functions911tcanelli41471

First post 03/22/18,Last post 03/22/18I need help doing a countif with checkboxes. I have a formula I need to countif in column Project if it has a value of Yes and column Parent Task is check. I also need it to countif column Project also has a value of partial. This for…Formulas and Functions511Lori Khoury

First post 03/22/18,Last post 03/26/18My totals were working finally on a summary sheet but then all the columns started changing to the same formula. I tried twice to go in and reset the reference sheet range but then it would just everything on the sheet. Anyone else have …Formulas and Functions11tcanelli41471

First post 03/22/18,Last post 03/26/18I am using the first row for totals and it is throwing my auto numbering system off. Is there a way to have the auto number start on row 2? Alternatively, if I put my totals on the last row, the web form will populate new rows below my tot…Formulas and Functions711jking

First post 03/22/18,Last post 01/21/19Is there a way to have a checkbox automatically checked if other columns with checkboxes get checked? I have 4 columns with checkboxes and instead of doing value columns for each one I would like to do a summary checkbox and just value on…Formulas and Functions849tcanelli41471

First post 03/22/18,Last post 03/22/18I need to sum or count the number of people in my sheet that are under 17 and over 65, based on who is checked. Can I do this using a cross sheet formula so all my totals are together? I have both DOB and age columns  can the formula be…Formulas and Functions112tcanelli41471

First post 03/22/18,Last post 04/01/18With this new feature I'm hoping I can eliminate abut 10 columns from one sheet and create a sheet just for totals. Currently I have several columns just for counting the value of columns with checkboxes. Do I have to leave these in the…Formulas and Functions1152tcanelli41471

First post 03/22/18,Last post 03/26/18Hi! I am attempting to create an "=If" formula that has 21 "if_then" statements. When I get to the end my formula enters as "Unparsable." Is there a limit to criteria in a formula? Thanks!Formulas and Functions11diana.blaschke28871

First post 03/22/18,Last post 03/23/18Hi, I need suggestions on how to count string of text on a column. For example, i need to count how may GRC are there on the sample below: GRC Aer, GRC Aer, CDIR, GRC HTM etc. etc. Thank you!Formulas and Functions1454ODT

First post 03/21/18,Last post 03/23/18I need help figuring out why this formula is returning Invalid Data Type: =COUNTIFS([Conversion Status]:[Conversion Status], "Won", [Date Requested]:[Date Requested], MONTH(@cell) <= "3", [Year]:[Year], "201…Formulas and Functions12Kate

First post 03/21/18,Last post 03/22/18I am trying to write a formula that has multiple steps to it. I want it to see if the project is completed then to give me the Max date of completion. (Image)Formulas and Functions113cedwards

First post 03/21/18,Last post 03/21/18Good Day All: I am trying to create the following Formula however I am receiving an Incorrect Argument: If impact is High and Influence is High, then the Classification is Key Player My Formula: =IF(AND([Impact on Project]2 = "…Formulas and Functions113Tony Barthelemy

First post 03/21/18,Last post 03/22/18I have a sales price database with one column for a retail price and another for a sale price. I'd like to make a 3rd column with a formula that populates the Save __% for our Marketing team. I made this formula which works with one small …Formulas and Functions11Nick Riek

First post 03/21/18,Last post 03/22/18Hi. Just getting to grips with Smartsheet. I have pairs of date fields: Forecast Date and Actual Date. I would like to set a red status symbol if the Actual Date is past/older than the Forecast date. How can I do this? Alternatively …Formulas and Functions1111Graham.Cole

First post 03/21/18,Last post 03/22/18Attempting to make a status bubble roll up where the parent will turn green if there are any blank ("") cells except for when all child cells are blank. Below are some of my failed attempts: IF(AND(COUNTIF(CHILDREN(), "&…Formulas and Functions211Jeffs

First post 03/20/18,Last post 03/22/18Hi All, I'm trying to do a SUMIFS, and whilst I can return a result individually using SUMIF, when I try and combine I get #INVALID OPERATION. The formula I am trying to use is =SUMIFS({SERVICE DETAIL}, LEFT(@cell, 3) = "Col",…Formulas and Functions1112Colin Janssen

First post 03/20/18,Last post 03/21/18Hello and thanks in advance for your help! I would like to automatically calculate the total # of "working days" left (as of today) by referencing the Due Date. I did find a formula for total number of all days left "=[Due D…Formulas and Functions1925mkrobo

First post 03/20/18,Last post 03/23/18Hi All, I'm trying to use SUMIF to sum the total of all rows that meet a specific criteria * =SUMIF([SERVICE TYPE]:[SERVICE TYPE], "Monthly Charge", [201712]:[201801]) This however returns #INCORRECT ARGUMENT. Not sure what …Formulas and Functions312Colin Janssen

First post 03/19/18,Last post 03/21/18I have a risks&Issues register sheet and would like to create a table in a new sheet which categorizes based on Streams and risk/issue rating. How do I do it. Attached is the sample. I would like separate tables for risks & issues…Formulas and Functions521Sachana

First post 03/19/18,Last post 03/19/18I have the following formula working fine! In short, based on the value of the “Application/Product” value, I auto populate the value of a Contact column. =IF([Application/Product]1 = "Edits", "Jane Smith", IF([Appl…Formulas and Functions4981bcase24986

First post 03/19/18,Last post 03/19/18I've been looking through the Community and can't find this...sorry if I've overlooked. I have a series of reports that determine if certain projects are within the last 30 days, such as added in the last 30 days, go live in the last 30 …Formulas and Functions2646bbemis

First post 03/19/18,Last post 03/30/18I am trying to ultimately set up my sheet to automatically send out Update Request 3 days (or whatever I set) within the "Bid Date" loaded into the sheet. It would be ideal if this update request would be emailed to the contact i…Formulas and Functions314Anthony Lunn

First post 03/16/18,Last post 06/01/19Hello everyone, I am trying to wrap my head around this one and I'm sure others have too. I want to count all the Flags that are raised under a grandfather (Administration) : the children and their grandchildren. Currently my solutio…Formulas and Functions627rstefanis41586

First post 03/16/18,Last post 09/26/18Hi all. I have a task list that consist of multiple parent rows with Children I have modifided so that if a task is delayed or dates are not added in the task will get a flag. What i would like is to have if one or more of the Cildren …Formulas and Functions925Simon Andersson

First post 03/15/18,Last post 03/19/18Hi  I'm relatively new, have watched videos and searched articles but I'm stuck. I have 50+ training products and a ton of potential job roles and location combinations. I'm trying to use SmartSheet as a "database" where I can …Formulas and Functions411coll3267

First post 03/15/18,Last post 03/15/18Is it possible to combine multiple formulas referencing the same cell without running into a circle reference? Similar to passing an object to a function. I would like to combine the 3 formulas below for efficiency sake rather than creati…Formulas and Functions114MarcRNelson

First post 03/15/18,Last post 03/16/18Hello! We are collecting weekly meter reading data (Meter Name, Reading Date, Actual Reading) via webform. I would like to create a calculation in a new column (within the sheet) that automatically calculates the weekly meter usage bas…Formulas and Functions921AshleyOrd

First post 03/15/18,Last post 03/15/18Hi, I am trying to convert a number in millions but shown as a thousand to the nearest decimal point and then add "M" to the end of the converted number. I understand the formula to divide by 1000 and then use the ROUND functio…Formulas and Functions1913MarcRNelson