-
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},…
-
Alphabetize Using Formula
Is there a way to easily alphabetize a list using a formula in smartsheet? In Excel, I would use the simple formula shown below to assign a numerical value to each unique list item. =COUNTIF([Unsorted Range],"<="&[Specific Cell Value]) This formula doesn't work in smartsheet, so I'm trying to find a solution. I am working…
-
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…
-
Dynamic year formula for the previous year
Hello! I would like to show the average score for the previous year without indicating specific year (instead of showing 2023 I use formula (YEAR(TODAY()) – 1. =AVG(COLLECT({Vendor Evaluation Sheet - Archive - Score}, {Vendor Evaluation Sheet - Task ID}, [Task ID]@row, {Vendor Evaluation Sheet - Archive - Created},…
-
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…
-
Simple IF(AND) Statement Returns Unparseable Error
Hello! I am attempting to use the following formula to return the margin value referenced in another sheet based on product type and volume: =IF(AND(Volume@row <= {M5}, {Type} CONTAINS(Type@row, @cell)), {Margin - Metals - 5k}) The product type ({Type}) and volume ({M5}) are also being referenced from the same sheet as the…