-
Issue with COUNTIFS formula with OR condition
Hi, I have an invalid operation error with the formula below. I want to count all active projects for environment = Reed Services, Reed Saas, V4. Any idea what's going on? =COUNTIFS([Project Status]:[Project Status], "Active", Environment:Environment, "Reed Services", OR(@cell = "Reed SaaS", @cell = "V4")) Thanks!…
-
Sum(children()) + sumifs (rows underneath, criteria 1, rows underneath, criteria 2)
I have a budget divided by department and subdepartment. the parent rows rollup the children rows to tell me the grand total for each subdepartment budget. I'm then creating a dynamic view of each subdepartment budget and giving the heads of those subdepartments the opportunity to add new rows (via dynamic view). I would…
-
Nested IF(AND) formula not doing what I think it should
This formula is returning Past Due when the Due Date is blank and there is a date in the Start Date field. This is not what I want. Instead, when I have a date in the Start Date field, I want it to say "Not Started" or In Progress based on the date entered. What am I doing wrong? =IF(AND([Start Date]@row = "", [%…
-
Moving latest entry into a new sheet
Hi, Would be great help if someone can suggest a solution to my below queries. Here is my scenario: I am collecting many column data through forms. Once in 2 weeks I collect status from project team so that I can share latest update to my leadership team. Queries: Is it possible to fill in already available row data into…
-
Can't COUNTIF if NO MATCH?
So strange - the formula appears to be right, but something isn't working... The formula looks across 18 columns to see if there are any that have the text "SrLead3" - an identifier for a job title. That identifier is in that row, but if #NO MATCH appears in any of the cells it is looking in for the COUNTIF formula, then I…
-
Hi, I am looking for a formula that will use drop down box to show RYGB
IF not started is selected from drop down the colour dot should change to Green IF in progress is selected from drop down the colour dot should change to Yellow IF Completed is selected from drop down the colour dot should change to Blue IF delayed is selected from drop down the colour dot should change to Red
-
2 Formulas Required
I'm trying to find a formula that will calculate the financial quarter based on the file review date I have used numerous formulas from the community but none appear to work. This is the one that's closest however, as you will see from the screen shot the quarters aren't calculating correctly =IFERROR(IF(MONTH([Date of…
-
Record a Date automation keeps updating to today's date.
I am using the "Record a Date" function to identify an Approval Date when the status of a request changes to "Approved." The automation does record the date once the status is changed. However, once the date is recorded, the automation continually updates the date to the current date. For example: If a request was approved…
-
Days Open if Task Status is Closed
I am new to Smartsheets and I am trying to show how long a task has been open since the project Start Date, but, if the task is marked as "closed", I want the "Days Open" column to record the Date Completed minus Start date, but if task is still Open or In progress, I want the "Days Open" column to record the number of…
-
IF formula to return text response based on max value
I am trying to write a formula that will return "risk avoidance" if the "risk score" is the highest value of 4 values. If Risk Score is greater than Quality, Efficiency and User Experience then return "Risk Avoidance". If Quality score is highest then return "Quality". If Efficiency score is highest then return…
