-
CONTAINS formula always returns no match
I have a Row Counter column created with this formula: =COUNTIFS([Task Name]$1:[Task Name]@row, OR(@cell = "", @cell <> "")) . It is designed to house row numbers so I can reference that number in another formula. I have the column Is Predecessor created with formula =IF(CONTAINS([Row Counter]@row,…
-
Use of WORKDAYS or NETWORKDAYS to return weekday date on a calculated date field
I have two kinds of calculated date fields in columns that work, but I need them to only return weekdays/workdays. Is it possible to use WORKDAYS or NETWORKDAYS on a calculated field to only return weekdays or workdays? Here are the date field examples: .....PlannedEnd2 equals PlannedStart2 plus 5 days......…
-
Is there a workaround that would equate to the IN Operator in MS Access?
I'm using Document Builder to populate an HR form that is used for multiple purposes. I have a single select drop down column with 17 pre-defined choices. Several of those choices require a new position number (PSN) to be entered and many more do not. In the instances that do not, I would like to create a formula to copy…
-
Count and Distinct Formula returns 1
Hi, I'd like to retrieve the total values excluding duplicates, this is the formula used: =COUNT(DISTINCT([Part Number]:[Part Number])) ; using the count formula only returns the total value of 3535, which is correct considering duplicated cells, I thought it was due to an error in the data format or something similar, so…
-
Display RYGB Circles based on dates in multiple columns
Need help trying to display the RYGB circles based on values in other columns. So, say we have columns that are ActualStartDateStep1, BaselineFinishDate, CalculatedFinishDate (meaning calculates on the fly as other dates are filled out, re-forecasting the possible finish date), and ActualEndDateStep7. (I have multiple…
-
Commas populating from an unknown place
I wanted to make metrics that tell me peak hours my team is receiving requests to staff accordingly. Since Smartsheet doesn't seem to be able to calculate time, I used the Right formula to grab the time and mixed in a find formula to to account for the change in character length. I currently have 2 sheets that calculate…
-
Syntax help for COUNTIFS with DESCENDANTS
Hi there! I have two separate COUNTIFS formulas that work individually on their own. However when I try to combine them into a single COUNTIFS formula I get an Incorrect Argument Set error. Here's what I've got: Successful Formula #1 - counting the descendants of Row 1 where the Simplified Status column is "Not Started" to…
-
Quick poll: Case sensitivity of LOOKUP and MATCH
Hi all, Our current LOOKUP and MATCH functions are case sensitive, e.g., "my task" will not match against "My task". Would folks expect these functions to be case sensitive, case insensitive, or do you have no preference? Thanks, Daniel
-
Get Average of "checkboxes checked" for an entire column reference from another sheet
Hi Everyone - I'm trying to get an average of "checkboxes checked" for an entire column range. I came up below but it's not working. =COUNTIF({1 Main Speaker System Range 2}, 1 / COUNT({1 Main Speaker System Range 2})) I was successful to get an average of a row range because I divided by a fixed number. Here is the…
-
COUNTIFS with month and year
The data that I have is on the Date:Date column. I want the formula to return the amount of cells that meet the month and year criteria. I have the following formula and I'm not sure what's wrong but it gives the #unparseable error. I have tried many different formulas from other posts but can't get it right.…