-
Formula to calculate 2 dates within 5days
Hello, I would like to write a formula that will calculate 2 dates based on this criteria. Titled: Percentage of Requests "In Progress" within 5days I'm creating a control sheet that includes percentages only On a separate (our main sheet where things are tracked) sheet I have a "created date column" and a "date put in…
-
Count that excludes blanks
I have the formula below that works perfectly, but I want it to exclude anything in the column "Phase Gate Criteria Identifier" that is blank. Can anyone help add that in? thanks!! =COUNTIFS([Stage-Gate Category]:[Stage-Gate Category], "Planning & Resourcing", Header:Header, 0, [Current Phase?]:[Current Phase?], 1, [Phase…
-
Issues with "OR" for multiple columns
Can someone tell me what's wrong with my formula? I know it has to do with trying to insert an "OR" in there but I've tried multiple ways and can't get it to work. Thank you in advance! =COUNTIFS([X Project Status]:[X Project Status], "Active", OR([Y Project Status]:[Y Project Status], "Active"), [Assigned to]:[Assigned…
-
Match COLLECT
Hello, I need a formula that compares two columns and returns a value in a third column if there is a match. For example, if there is a Match of Jake, Smith then it will return the dept name Name Dept Lookup Name Dept Jake, Smith Science Jake, Smith (Return Value) Thanks
-
Combining Countif & OR Function
I am trying to write a formula that will count the number of rows where either column A (External Successor) is not blank OR column B (Internal successor) is not blank. =COUNTIF(OR({Internal Bench} = <>"", {External Bench} = <>""))
-
Clickable phone numbers in reports
I need to have phone numbers clickable so we can click a phone number from our phone rather than copy/pasting it. Is there a way to do this? I thought embedding some html code would be an option, but that's not working. This is the logic I have that pulls the phone number into my report from a cell. =[Primary First Name]1…
-
Display negative numbers as Zero and Stop count when complete
I have a Smartsheet which I have a column that is counting the remaining days until the target due date. =[Target Finish]@row - TODAY() I would like for the remaining days to stop counting once task is marked as "Complete" in status column. I am also interested in preventing the number count from going negative and just…
-
Counting between dates greater than or less than a date
Hello, I have the following formula coming back as unparseable. I am trying to calculate how many OOB Ambients in each month of the year. I normally have a date sheet that I reference, but I'm thinking using the date function might be cleaner. Can someone look at the formula below and identify my mistake? =COUNTIFS({OS…
-
Using sheet summary fields within sheet formulas
Hi, I have a problem where I am trying to use smart sheet to generate the sell price of products based on a fixed % margin. Using a margin that is fixed is simple, I just put the value into the column formula. However, if I want to adjust the margin, I had hoped to use the sheet summary to do this. I have used sheet…
-
Need help correct a complex formula
So, I am working with this formula, which works great, but I need to throw in a CONTAINS aspect into it...and what I am trying is not working. Current formula (that works): =AVERAGEIF([Project/Task/Issue/Risk Name]:[Project/Task/Issue/Risk Name], ="Stage 2", [Actual Duration]:[Actual Duration]) How would you add in the…
