-
Best way to auto Sum cells with both numbers and text?
Hi everyone - So i am trying to find a way to sum values in cells containing both number and text like "1 Macbook Pro, 2 Lenovo Tiny PC, etc. and give me the total for each item type in the totals column. Please see the screenshot of my example smartsheet. All of the column values are drop-down list of things we usually…
-
Formula to calculate two week iteration
Our company works in two week chunks we call iterations. Using the following formula in Excel I am able to calculate what the current iteration is. However when I import from Excel Smartsheet doesn't appear to like the formula. Using the current date the formula in Excel is =FLOOR.MATH(DAYS(NOW(),"6/11/01")/14) Using a…
-
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…