Formulas and Functions
Archives for July 2020
Discussion List
-
Countif - Multiple outcomes?QuestionFirst post 07/14/20,Last post 07/14/20Hi, We run a design Dept sheet accessed by 5 sales reps who can select 1 Request Type from 10 set options that don't change (RT column) IE: Drawing OR Drawing and costs OR TSR OR Permit etc.. These I count easy enough rep x rep (countifs…Formulas and Functions40Jason P
-
COUNTIF(S) date rangeQuestionFirst post 07/13/20,Last post 07/13/20I know I've seen several questions regarding counting within a date range, but I can't find something that covers this problem. I can get a COUNTIF to work with each half of this formula (Range, Criteria), but I can't get them to work toge…Formulas and Functions60Neree Jackson
-
Nested IF/OR Contains FunctionQuestionAnswered - Pending Review Chloe Rose - 8PCC 6 views 1 comment 0 points Most recent by Andrée Starå Formulas and FunctionsFirst post 07/13/20,Last post 07/13/20Hi, I'm trying to write a CONTAINS formula to auto-populate the project location column. There are 6 states to choose from. The Project Name is usually very long & descriptive but always contains the standard 2 letter code for the six…Formulas and Functions61Chloe Rose - 8PCC
-
Sum if and VlookupQuestionFirst post 07/13/20,Last post 07/13/20Hello there, I need to add all quantities of a column which have similar characteristics mentioned in another column. It must be very easy but I couldn't do it. I am going to use a VLOOKUP command to find all the items of similar type, the…Formulas and Functions50Mehran
-
Answered - Pending Review Christopher Loh 10 views 4 comments 0 points Most recent by Andrée Starå Formulas and FunctionsFirst post 07/13/20,Last post 07/13/20I am building a headcount tracker that has Employee Name, Employee ID, Manager Name, Manager ID as some of the fields. The Manager Name is a dropdown where I can select the Manager. Is there a way to tie the Manager Name and Manager ID fi…Formulas and Functions104Christopher Loh -
First post 07/13/20,Last post 07/13/20I'm wondering if it's possible to have a formula referencing an external cell by using an external column reference and a non-absolute row reference. Basically, is this {ext.column}@row possible like [column]@row is?Formulas and Functions90adrian1234
-
Accepted Answer Steve Joyce 5 views 7 comments 0 points Most recent by Andrée Starå Formulas and FunctionsFirst post 07/13/20,Last post 07/13/20If LEASE END is TODAY() then checkbox = 1.....Formulas and Functions57Steve Joyce
-
Collect (Sumif xxx)QuestionAnswered - Pending Review Jörg Schmidt Novum Hospitality 426 views 1 comment 0 points Most recent by cmondo Formulas and FunctionsFirst post 07/13/20,Last post 07/13/20Hi, I have following challenge ;-) my sheet: reason costs reason1 x € reason2 y € reason3 z € how can I calculate in a formula how much Costs are depending on various reasons?Formulas and Functions4261Jörg Schmidt Novum Hospitality
-
Question Constance King 10 views 0 comments 0 points Started by Constance King Formulas and FunctionsFirst post 07/13/20,Last post 07/13/20(File) (File) (File) I have multiple sheets tracking different aspects of grant submissions. I want to bring them all together into one sheet. I need to match items based on two columns and pull data from a third column. We have 24 countie…Formulas and Functions100Constance King
-
Help needed: COUNTIFS and DOES NOT CONTAIN functionAccepted AnswerAccepted Answer jmoser 8 views 3 comments 0 points Most recent by Paul Newcome Formulas and FunctionsFirst post 07/13/20,Last post 07/13/20Hi team - so I have a formula for counting "errors" that occur in a specific month: =COUNTIFS([Main row]:[Main row], 1, Created:Created, IFERROR(MONTH(@cell), 0) = 5, [Incorrect format]:[Incorrect format], 1) What I'm trying to d…Formulas and Functions83jmoser
-
Sum IfAccepted AnswerAccepted Answer Lockie Stewart-Baker 8 views 7 comments 0 points Most recent by Andrée Starå Formulas and FunctionsFirst post 07/13/20,Last post 07/13/20Hi All, I need help with a formula. I think it should be sum if. I want estimated hours 28 to show the sum of cells 29:34 but only if %complete is less then full. Thanks. (Image)Formulas and Functions87Lockie Stewart-Baker
-
Answered - Pending Review Yashwant Singh 24 views 13 comments 0 points Most recent by Yashwant Singh Formulas and FunctionsFirst post 07/13/20,Last post 07/13/20Hi, confused for using Today() formula in my sheet.. Currently using countif and sumif formula based on today() date. please confirm that formula automatically changed when date today() change or when I can manually activate my sheet first…Formulas and Functions2413Yashwant Singh
-
Answered - Pending Review Michael Murphy 10 views 3 comments 0 points Most recent by Andrée Starå Formulas and FunctionsFirst post 07/13/20,Last post 07/13/20I have several vendors working on the same project. Each has a set of required deliverables that have to be submitted at regular intervals (weekly, twice monthly, monthly) during different project phases (concept, design, installation). I …Formulas and Functions103Michael Murphy
-
Formulas and attachmentsQuestionAnswered - Pending Review Justin Peter 12 views 1 comment 0 points Most recent by Stefan Formulas and FunctionsFirst post 07/12/20,Last post 07/13/20We utilize SmartSheet to collect information for complex implementation projects. We have applications where our users will be attaching documents to specific items/rows in a sheet. It would be awesome to be able to write various formulas …Formulas and Functions121Justin Peter
-
Possibly a Match/Index or a Vlookup Question. Conditional formatting for a lookup table.Accepted AnswerAccepted Answer John Teh 11 views 2 comments 0 points Most recent by John Teh Formulas and FunctionsFirst post 07/12/20,Last post 07/14/20Hi everyone, I'm currently having an issue trying to pull the names of the team members with the highest score in each team. I am able to pull the highest score on each team with a formula that looks something like: =MAX(COLLECT({Smartshe…Formulas and Functions112John Teh
-
Answered - Pending Review Cheryl Moon 19 views 2 comments 0 points Most recent by Cheryl Moon Formulas and FunctionsFirst post 07/10/20,Last post 07/13/20I am calculating labor costs based on project duration. I need to know the labor dollars only for the months greater than the current month (for example, if this month is July/2020, then only calculate August/2020 through February/2021. Th…Formulas and Functions192Cheryl Moon
-
First post 07/10/20,Last post 07/13/20I am trying to import an excel spreadsheet where I have names in a column on each row. The names are formatted as FirstName<sp>LastName, and they are being imported then the cells are being referenced in other SS grids. I would lik…Formulas and Functions171Kevin Smith
-
Weighted Sum of CheckboxesQuestionAnswered - Pending Review Nick VanHouten 10 views 1 comment 0 points Most recent by Stefan Formulas and FunctionsFirst post 07/10/20,Last post 07/12/20I'm looking for a method of creating a "score" based off the sum of checkboxes in a row. Various columns ideally would have different weights, creating either a numerical sum or a percentage out of 100. I'm stuck at having a chec…Formulas and Functions101Nick VanHouten
-
Countifs with Different Named RangesAccepted AnswerFirst post 07/10/20,Last post 07/13/20Hello again all! I've run into an #INCORRECT ARGUMENT SET when trying to reference two ranges in a countifs() function. I believe the error is because my Project Range only references to one column, and my {Visits} range includes 12 column…Formulas and Functions187cmondo -
Answered - Pending Review Stephen Paolicelli 20 views 10 comments 0 points Most recent by Stephen Paolicelli Formulas and FunctionsFirst post 07/10/20,Last post 07/13/20I can get the count with =COUNTIFS({2020-03 Monthly Complaints Range 1}, =[Manufacturing location]1) but when I add the filter >= to a date referenced in a cell. I get the unparseable error. =COUNTIFS({2020-03 Monthly Complaints Range…Formulas and Functions2010Stephen Paolicelli
-
Answered - Pending Review Tori Isbell 11 views 3 comments 0 points Most recent by Paul Newcome Formulas and FunctionsFirst post 07/10/20,Last post 07/10/20I am sending an Update Request (UR) form to employees to have them tell me how many hours they worked each day. The smartsheet is prefilled with 8 hours each day. So when the UR form get to the employee, they just make adjustments as neede…Formulas and Functions113Tori Isbell
-
Answered - Pending Review Katye Reed 13 views 25 comments 0 points Most recent by Paul Newcome Formulas and FunctionsFirst post 07/10/20,Last post 07/13/20Hello, I am looking to determine the average time based based on start/ completion dates, location and the frequency in which the activity occurs. However, it does not seem to be calculating based on my criteria. Could anyone help me det…Formulas and Functions1325Katye Reed -
Answered - Pending Review Doyle54 8 views 3 comments 0 points Most recent by Paul Newcome Formulas and FunctionsFirst post 07/10/20,Last post 07/10/20I am wanting to have a shared user be able to input a Startdate and EndDate which is then calculated using Countifs. Is this possible in a Summary Sheet? I get an unparseable error for the code below. Any suggestions? Thanks. c (Imag…Formulas and Functions83Doyle54
-
SUMIFS Cross referencing HelpQuestionAnswered - Pending Review StaceyT 21 views 10 comments 0 points Most recent by RossL Formulas and FunctionsFirst post 07/10/20,Last post 07/13/20I keep getting an "incorrect arguament" or "inparseable" return when trying to sum up the amounts in a given range based on 3 criterion. The sum range & both criteria are in another sheet. The first criterion is a s…Formulas and Functions2110StaceyT
-
Answered - Pending Review Monica Gallegos 12 views 5 comments 0 points Most recent by Paul Newcome Formulas and FunctionsFirst post 07/10/20,Last post 07/13/20I have a metric sheet and I'm currently using a CountIF formula to count the locations in my sheet, works great. But I'd like to take it a step further and I want to group the location entries into APAC, NA, Canada, & EMEA. I'm trying …Formulas and Functions125Monica Gallegos
-
Average If formulaQuestionAnswered - Pending Review Gillian Pritchard 13 views 4 comments 0 points Most recent by Paul Newcome Formulas and FunctionsFirst post 07/10/20,Last post 07/10/20Looking for help with an average function if anyone can help, we have a column with a company name (SI Team) and a column for response time (this gives number of days). Looking help with a formula to count up and average all the response …Formulas and Functions134Gillian Pritchard -
Answered - Pending Review Lori Moore 6 views 10 comments 0 points Most recent by Lori Moore Formulas and FunctionsFirst post 07/10/20,Last post 07/10/20Hello, I am attempting to run a report with multiple sheets and/or a workspace (I have tried it both ways) the criteria seems to work fine but when I attempt to show a column from one of the other worksheets it does not populate. Any sugg…Formulas and Functions610Lori Moore
-
First post 07/10/20,Last post 07/12/20Hi, Please help me on writing a formula to calculate the weekly sales data. Please see the attached screenshot of my example data. (Image)Formulas and Functions177Benjo09
-
Copy and move row workflow disabled by smartsheetAccepted AnswerAccepted Answer Katherine Britt 9 views 5 comments 0 points Most recent by Andrée Starå Formulas and FunctionsFirst post 07/10/20,Last post 07/11/20My copy/move row workflows (that previously worked) were disabled yesterday. I now have an error that says "Rows can't be added to the destination sheet because a column has the same name as a system column on the source sheet." …Formulas and Functions95Katherine Britt -
COUNTIFS and NOT FunctionQuestionAnswered - Pending Review Silvia Frei 12 views 2 comments 0 points Most recent by Silvia Frei Formulas and FunctionsFirst post 07/10/20,Last post 07/10/20I'm looking to count the number of instances an epic is listed, but to exclude Closed and Done ones. I am trying this formula which gives an error =COUNTIFS(JIRA:JIRA, CONTAINS("TPM", @cell), [Issue Type]:[Issue Type], "Epic…Formulas and Functions122Silvia Frei
