-
Formula to Count Non-Duplicate values
Hi, I need to count the Non-Duplicate values in a given range. Could you please help me out with the formula. In the attachment 1 & 2 is not repeated in a range and I need answer as 2 via formula.
-
Unable to determine if a field contains a date less than today's date (involves vlookup).
Hi all, I am trying to create a helper field to allow me to generate a report of records where the date from a field in my Smartsheet is less than today's date. This will be used to call attention that an update is needed. The date field being used is a vlookup generated field from another sheet. Formula: =VLOOKUP([Code]1,…
-
Counting AVG Days
Might be a simple solution, but I've been playing around with it long enough and give up. I'm trying to calculate the average amount of days to complete a process, for example; Process Start Date End Date Process 1 09/03/2022 09/20/2022 Process 2 09/06/2022 09/16/2022 Process 3 10/01/2022 10/10/2022 And so on. What I am…
-
Automate INDEX/MATCH or VLOOKUP the first date of the week each week.
I want the "Week of" column to automatically show the date of the first day in the current week each week starting with Monday as the first day, and if "Week Of" and "Project Name" both match on another sheet where the data is being enter then I want to return the next 4 columns for the rows where the "Week Of" and…
-
Cost formula with a multiple select drop down column.
Hello, I am having trouble with a cost formula. I am trying to sum the total cost of a certain department. The department is in a multiple select drop down column. They formula will only sum the departments with 1 dept selected and will not pick up the cost if the column has multiple departments. See screenshot below. Fire…
-
How to Pre-Populate a Field in a Form?
Hello Smartsheet Community, I am trying to pre-populate my Work Order ID field in a form with the number it is assigned in its row. A while back, we were given a complex formula to include specific fields but it is very complex and hard to understand. Can someone please let me know how to add the Work Order ID to the form…
-
Date Expected Error or decimal return
I'm trying to return a date value based on the week number identified. So if I have "Week One" in my training date number, I want the Date column to return 10/17/22. I'm trying to use an IF formula to do this but keep getting #DATEEXPECTED error. When I try to convert the column type to text/number instead, it gives me a…
-
Isolating all text to the right of a character (e.g. "-") in a string
Hello Smart People - I'm parsing a string using helper columns. I want to return all text to the right of my last "-". For example, QA-IS-OPS would return OPS, and QA-ARC-RM would return RM. Is there a simple way to do this? Thank you!
-
How to exclude cells with formulas when using SUM formula for column totals
I'm wanting to use a formula to get the total $ amount for 2 columns in a sheet. The problem is, both columns contain cells w/ SUM formulas. I do not want to use or include these existing formulas in my new formula. There are 4 cells with formulas I want to exclude and some cells with text. Is it possible to get the total…
-
Count how many cells are between two values
So, the first one was pretty easy =COUNTIF({Export Range 5}, <30) - that works But, now I need to count the number of cells between 31 & 120; I just can't seem to make it work. =COUNTIF({Export Range 5}, >30 and <120) just gets me "unparseable". =COUNTIF({Export Range 5}, >30, <120) gets me "incorrect argument set". What…
