-
Nested Index/Match if Partial Text
I require a cross-sheet formula that will search for a Task (row) in another smartsheet, if the Task ‘Comment’ (column) contains the word “All”, then go to another Task row and index the Date. If the Task ‘Comment’ (column) does not include the word “All”, display the original Task Date. For example: If Task 4 Comment…
-
Excluding duplicates from formula
Hi there! Had a wonderful talk with a Pro Support the other day, and he helped me write an incredible formula for counting "Left to Sell" value on sales items our team is selling. However, I noticed after the fact that it is counting each item as 1 sale, and there may be some sales that cover multiple items but should only…
-
Sumifs, Constains, and Dates
I'm trying to calculate the average time it takes to close a ticket. I know that I want to use SUMIFS/COUNTIFS. The COUNTIFS formula works fine: =COUNTIFS([Initiative/Program]:[Initiative/Program], CONTAINS("Metrics/Reporting", @cell), [Start/Raised]:[Start/Raised], AND(@cell <= TODAY(), @cell > TODAY(-30))) I'm struggling…
-
COUNTIFS and CONTAINS
Hi! I need some guidance for a count analysis of our RAID Log (Risks, Issues, Decision). In the RAID Log source sheet, we have a column where the team selects the Project(s) the risk or issue is tied to. The Project column is set up as multi-select. In a separate Count RAID sheet, I'm using this formula: =COUNTIFS({Risk &…
-
Rollup of contacts into a single cell as a single select or comma sep string?
I have a project plan with 100 tasks. Each task can have one or more assignees in the Assigned To column. I want to have a rollup of all possible assignees into a single cell for project filtering (as opposed to task filtering). I understand i can not do a formula with multiple contacts. I am OK to translate this into a…
-
Formula if cell is blank then, if cell is not blank then....
Formula gods, looking for help, please. I'm trying to write a formula that says if the finish date is not blank, then the QA status is Green, if finish date is blank, then go to the days open field and if less than >= 60 and <=89 then Yellow, if days open is >=90 then Red. I can write the individual formulas to get the red…
-
Status If/And Formulas
I have a project manage sheet I'm managing that I can't seem to get the formulas right in. The first one has two parts. 1) I want the status to be green if the Planned Completion Date is equal to today or the future. The Status to be Yellow if the Planned Completion Date is 1-7 days in the past and the Status to be Red if…
-
Help with Formula
I am trying to create a formula =Program Revenue+PY Rollover-Total Booked however I think because Program Revenue is also a formula it is causing an error. Help is appreciated.
-
Behavior of "=" Operator
On several occasions I have confused myself, not understand why I am getting a #NO MATCH error when comparing a Text/Number column to a Auto-Number column, and I have realized why... Results in False 001 = 001 099 = 099 Results in True 100 = 100 10000 = 10000 I understand why this is happening, but I do not necessarily…
-
Combine values in column only if value in each row is less than a value
I have a column with an identifier in each row and in another column I have a value in each row. I want to search the second row for any values that are less than a reference cell, and if less that that reference cell, I want the text from the first column to be displayed in the cell the formula is in separated by , if…