-
IF Formula with Multiple Criteria
Hi all, Working on an IF formula with 3 conditions. IF Value = 0, "No probability of Risk: Functionality requires evaluation and/or validation when/ if the functionality is intended for production use." IF Value, 2-36, "Low probability of Risk: No Computer Validation testing required." IF Value, =>36, "Medium probability…
-
How to write a formula with multiple conditions?
Hi, I have these 5 Phase columns in my project tracking sheet. Now i want to automate the column named "Test" that shows at what phase the project is currently in. The logic is something like: "If the Phase 1 is 100%, Phase 2 is 45%, Phase 3 is 65% then the project should be at Phase 2" Ideally if the phase is at 100%, It…
-
How to apply a priority countif formula to specific rows based on cell criteria?
Hello, I am using the following formula in a priority column to maintain ranking number when filtering my sheet. I would like to apply this formula to the rows based on criteria in my "Open, Backlog, Closed" column. For instance, if a closed is selected for a row, i do not want the formula to apply to that row.…
-
Generate Random Lists
Is there a function/formula that can take a list of names and randomly assign them to groups? Example: If I have 20 individuals that I want to assigned to groups of 4: Group A, Group B, Group C, and Group D Column 1 has list the names of the 20 individuals Column 2 is the output field for where the formula would go to…
-
I'm just stumped on this date function. Can someone help me please?
I have a sheet that is tracking projects for our team. I have one column that is "Asset Due Date" and I want another column "TD Due Date" to auto populate a date that is 28 days prior to the date chosen for "Asset Due Date". Both columns are set up as date column types. I've played with the date function and just can't…
-
Flag duplicates with conflicting dates
Hi, I have a list of names with a starting date and a finishing date. I am trying to get a formula that flags conflicting dates for the same person (duplicate). On excel I sorted with a SUMPRODUCT that I know smartsheet does not support. I have also tried to break it down so it adjust to smartsheet but I can't solve the…
-
Formula to identify with min/max date if column is less than 1 and date is less than today?
how do I do this? I have the below which grabs the count correctly for anything less than 100% and <= today's date, but I want to grab the min and max date. =COUNTIFS({Estimating Percentage}, <1, {Est Start Date}, <=TODAY())
-
Why is my formula #UNPARSEABLE
Hoping someone can help... I am trying to get a count of projects opened and closed based on week number and year. Range two below is the year column in another sheet (the column I'm starting with is 2022, but I will be expanding through 2026 once the formula works). Range 1 referenced below is the week number, as…
-
Error calculating time
I believe I have read through every thread on this topic and it has been extremely helpful. However I'm getting a weird error that hopefully someone can help me with. I'm getting a completion time of "10:05 04pm" in row 3, I'm sure this is due to the amount of time for the task - I just don't know how to write this so this…
-
Using COUNTIFS with multiple criteria and OR
Hi, I'm trying to count the number of rows that meet the following criteria (I'm getting an "Incorrect Argument" error. Project Complete is not checked Project Cancelled is not checked Sales & Leads is not checked Month is August Year is 2022 Deployment Type is either New Deployment or Package Upgrade =COUNTIFS({Project…