-
IF Statement with nested OR
I am trying to write a formula and I am not entirely sure if it is possible. I have 3 columns, Status, Internal only, and Archived. I am trying to write a formula for the status column to mark it Green, Yellow or Red. If the archived column is blank and the Internal Only column is unchecked, I need it green. If the…
-
Count when multiple drop-down selections are chosen
I have a "Project Type" column that allows for multiple drop-down selections: Manufacturing Site Change, Process Change, Vendor Change, etc. I am trying to count each individual project type even if multiple selections have been made in a single cell; e.g., if I've got Manufacturing Site Change and Process Change both…
-
How to create formulas when multiple rules are involved?
Hi I am trying to build the following: There are 5 criteria that will indicate the Project Health Status. The Health Status will be base on the following rules: 1) Green: 4 indicators or more in “Green”, none in “RED”. 2) Yellow: None in “Red”, with less than 4 indicators in "Green". 3) Red: One or more indicators are in…
-
Calculating Project Completion status using text via sheet summary
Hi all, I am trying to calculate the overall completion of the project based off of trying to calculate the number of tasks with "Completed" as a status, dividing it by the total number of statuses in the "Status" column. I can't seem to figure out the formula to give me that percent. Here is what I am using; HELP!:…
-
Creating an Expiration Countdown
Hello, I need to create a tracker that counts down when a contract is going to expire. I am trying to use NETWORKDAYS function and it is not working for me. Does anyone have any suggestions on how I can do this? Things to keep in mind is that this would be a living spreadsheet so the days to expiration would have to change…
-
Updating Existing Formula: Counting Metrics
Hello! I am working on a formula that I had once before that is not working when I transfer it to a new sheet and update the names of the rows that I wanted to add. Prior Formula: =SUMIFS(CHILDREN(), CHILDREN([Recruiter Name]@row), AND(@cell <> "Full Time >0.5", @cell + ROUNDDOWN(SUMIF(CHILDREN([Recruiter Name]@row), "Part…
-
IF AND formula help
Hello, How do I use an "IF(AND(" formula to get a count of two conditional columns? I am working on calculating a weekly average and I have a date received column which I broke down by "week number:" and "year" in separate columns. However, I have two separate Smartsheets, one being the current task and the other being the…
-
If / then statements with subtraction problem
Hello everyone, I was hoping some of the pro's on here could help me figure something out. I'm creating a hours tracker for employees to use remotely and I need to calculate the overtime hours as anything over 8 or anything over 10 depending on the answer given in a column. So if [certified] = "Yes" then subtract 8 from…
-
Can you have a formula populate in a child row automatically?
In our project tracking sheet we have a column for Project/Task Name. The project name is listed in the parent row and then any tasks are listed below as child rows. The projects are added to the sheet by the Director who also assigns a project manager. The project manager then adds tasks and indents them as child rows…
-
Sum Only the Cells That Meet Certain Criteria
I want to sum the Estimated Number of Work Hours in a column, but only in those rows where the Status says "In Progress". Is this possible? Thank you in advance!
