-
Updating Date Column if Another Column has been Changed
I am trying to create a formula to change a Date column to update to the current date when another column has been changed. I have used the TODAY function within IF but this updates the current date every time the sheet is opened and saved. I only want the Date cell to change when a specific other cell is changed.…
-
Using a formula to Reference another sheet with two variables
I'm trying to create a formula for a report that references another sheet but it needs to reference and check two columns. My crude formula below to illustrate: =COUNTIF ((RefSheet1Range1)="Windows Server" AND (RefSheet1Range2){Status}="Completed") This would return a count of the number of Windows Servers that have been…
-
Ignoring Blank Cells
How do I make this formula ignore blank cells? I want it to total the percent of YES cells to NO in a range. =COUNTIFS(CHILDREN(), OR(@cell = "YES", @cell = "No aplica")) / COUNTIFS(CHILDREN(), OR(@cell = "", @cell <> ""))
-
COUNTIFS, WITHIN DATE RANGE, ISBLANK FOR ADDITIONAL DATE RANGE
I need to write a formula for counting active projects that are past due for a current month based on schedule complete date being in that current month and when actual complete date is blank. Project Status = Active Schedule Complete Date = 7.1.20 - 7.31.20 Actual Complete Date = Blank Here is the data and fields I would…
-
Sumif in a RYG symbol column
Hello, I have a formula that returns a Red, Yellow, or Green icon depending on criteria in another column. Red, Yellow or Green correspond to a priority in our work order flow. Then, in a metrics summary sheet, I have a Sumif formula based on the status (Red, Yellow, or Green). At times, we need to override the formula,…
-
"Smartsheet Formula Examples" template cell references issues
The "Smartsheet Formula Examples" template, while very helpful, appears to have a variety of issues with cell references that have been moved over time. Somewhere around row 26 things start to get weird. Is this only on my copy of this sheet? I did delete it from my workspace and reload it to see if it would be fixed, but…
-
IF(OR) trying to OR 3 expressions
From what I can see I should be able to 'OR' 3 expressions, but I keep getting unparseable. =IF(OR(Required@row = "Full Topo including underground services",(Required@row = "Full Topo excluding underground services",(Required@row = "Drainage trace only"), "Yes", "No") Any help appreciated. Regards Paul
-
Trying to create a simple IF..OR..ISBLANK formula
I'm getting an invalid operation error and I know I'm making a simple mistake but I've tried everything... =IF(AND(NOT(ISBLANK([Launch Date/Prod Release/Go Live)]4)), >=TODAY(-7)), 1) IF the Launch Date/Prod Release/Go Live cell is not blank and the date in the cell is greater than or equal to the date 7 days ago, the…
-
Calculate number of days a task is later than forecast end date
Hi, Can somebody help? I need to add a column that calculates how many days late tasks less than 100% complete are compared to today see below example, basically need a formula that calculates the 'working days late' column. Thanks forecast end date % Complete working days late (based on todays date) 3/8/20 99% 2
-
Return a sum using using sumifs and dates
My objective is to return a sum of all 'Work Hours' that are in a department (Wire in this example), that fall within a date range of the 'END' date that Smartsheet is reporting.. This formula returns a value, but I've checked manually, and I've determined the value is incorrect. I've exported a filtered view of the sheet,…