-
Unparseable for multiple IF statements
Hi, I'm trying to use multiple IF formulas within one cell by transferring from the Excel sheet I created it in, but I keep receiving the "#UNPARSEABLE" error code. Here is the formula I've attempted in SS: =IF([Support Intensity]@row="high", 9),IF([Support Intensity]@row="medium", 6),IF([Support Intensity]@row="low", 3)
-
Identify MAX Date for each employee
I have created a list that tracks the number of days an employee is out. I need to identify the most recent absence. An employee can be on the list more than once. I would like to use the MAX function to identify the most recent absence for each employee by checking the box in Max Date. Is there a way to do that? The…
-
COUNTIFS Function with an "Or" function included?
Hi all. I am currently trying to create a COUNTIF function that has 2 required parameters, and then 4 "or" parameters. Basically, the formula should count if the RAID Log box is checked AND if the RAID Item is Risk. Then, it should count if the item's status is "new" OR "in progress" OR "deferred" OR "ready for close".…
-
IFERROR Function Struggles
I have a working formula that I need to add an IFERROR function to so that the cell is blank when there are no values to satisfy the formula instead of receiving an #INCORRECT ARGUEMENT SET response. Here is the working formula without the IFERROR: =MEDIAN(COLLECT({Resi - PC2 Testing Duration}, {Resi - Enter Stage 7 Date},…
-
Index column with multiple criteria
I have two "Slack Additions" and "Todays Location". Slack Additions is populated from a Slack Workflow. It is roster of peoples names, location, location start date and location end date. Names can appear on this sheet multiple times, with different locations for different time periods. I am trying to write a formula in…
-
SUMIFS with a Multi-select Contact Column
Is there a correct way to add contacts into a formula? I am trying to sum the number of attendees per VP (could be a couple VP's in this multi-select contact column) training request. Do I need to type in the full name? Only part? Add in a special function? Details: Completed Projects - Team X # of Attendees = number of…
-
Help with IF(INDEX(MATCH
Hi all, First question in the forum. I'm in need of help with an INDEX(MATCH( formula that I've been using but now need to make conditional on another column. Essentially, I only want the INDEX(MATCH( formula to return a value when a column "Completion" is changed to "100%". I've tried nesting the existing INDEX(MATCH…
-
Hyperlink to corresponding row in external document
I have a 600 row Smartsheet and need to provide hyperlinks for each row to an external Excel document. At the moment, a basic hyperlink will open Excel but place the cursor at the top of the sheet. Can I create a hyperlink formula to match the reference numbers on each row? Link for Reference 010 opens Excel document with…
-
Help with formula: return value from cell in same row if criteria is met
Hi, I am trying to figure out how to write a formula to return a value from the SAME ROW, when criteria is met. I can't figure out what I am doing wrong with index/match/collect - I am hoping someone can help! Most of the tips/tricks/Community answers I have seen are using columns not rows, so I am not sure how to do it. I…
-
Using AVG(COLLECT())
Good afternoon, I'm trying to do the equivalent of '"AVERAGEIFS". I read about using the collect formula, but I'm having an error. The formula I'm using is: =ABS(AVG(COLLECT({Master Sheet - 1960 - Net}, {Master Sheet - 1960 - Date}, Date@row, {Master Sheet - 1960 - Info}, [Summary Info]@row))) I'm trying to calculate the…