-
Index Match with multiple ranges?
Hi All, I am stumped on a formula and hoping for some direction. I am attempting to put together an Index(match) that will bring in the "order number" from sheet two to the "Assigned to Contract" column, if the unique identifier appears in any of the "Unique ID columns" (Columns in between hidden for better viewing). Is…
-
How to calculate the date given the year and yearday?
I basically want the inverse of YEARDAY(date) function. If I have the 1-365 year day number and the year, is it possible to create a formula to result in the DATE?
-
I need a formula to enter text in a cell when a value in another cell is between a range of numbers.
I am stuck on this formula. When the Hand Sink Temp column is less than 100 or greater than 110, enter 'alert' in the Hand Sink Temp Alert column. This is what I have tried as I have a similar situation where the formula works. This scenario is just enough different that I keep getting UNPARSABLE. =IF(AND(Hand Sink…
-
I am trying to extract the first letter.
Hi Community, I need to extract the first letter from other cell. Please check the formula and help me. Please help me to get the correct values, If 'e' represents in the duration column then it should be '7' else '5' Thanks In Advance! Cheers!!
-
Countifs Error for 3 references
I am trying to get a metrics report for a dashboard for a log with multiple test cycles and multiple data. My metrics are by Status (New, Open, Cancelled, Assigned to Vendor, etc) Each of the Status options has to be filtered by the testing cycle which is either SIT or Deferred to SIT (I know I will have to do the same…
-
Add one more condition to check project status
Hi @Paul Newcome I'm able to calculate projects falls under on time and delay for the month, I would like to check "project status" column and check for "completed", =COUNTIFS({Month}, Label@row, {OTIF}, [Category 1]$4) im using this formula currently,
-
Formula to return the average of points assigned to a specific person within a specific date range
Hi all, I have a fairly complex formula I'm tying to create, and can't manage to make it work. Let's say my data sheet has three columns, representing a point value given to an individual on a specific date: Person (Number/Text, each cell contains one name) Points (Number/Text, each cell contains one number) Date (Date,…
-
If/Index/Match help!!
Hi! I am relatively new to Smartsheet. Usually I can poke along and work through to get the result. Today, not so much. I am trying to pull data based on data input. With the data, I need to match and select from one of 3 columns on another sheet. I have attached a couple screenshots. User selects accumulation from the…
-
Column formula to sum children
I am struggling with my formula. I have a helper column that identified Parents (1) and Children (empty) I have a column that contains a value for all children, I like to create a column formula that always provides a total of all children values at the parents row (yellow) when the parent has children. My formula has an…
-
Sum dollars based on names in multiple columns
I have a shared tips sheet. A big tip is divided by the coaches that are coaching for the group. If John is coach 1, Dave is coach 2, and Skye is coach 3, then the $30 tip would be split $10 each. The next group may have Skye , Mike, and Nate and may have a $60 tip. Etc. I am trying to write a formula that says of Skye is…
