-
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…
-
Show next pending task in Summary
Hello, I am trying to show the next pending (open) task in Summary, so it will show on my Summary report as well. In the summary report I also want to show the delivery date for that task. Basically if the previous task is Completed (checked), then I want the next one to show in Summary. I am trying an IF and VLOOKUP…