-
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!…
-
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…
-
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…
-
Cross-Sheet Formula:
Hi, I would like to ask the community on how the 100,000 inbound cell cross sheet reference is computed. Using the following example: If I have a sheet with 10 columns and 100 rows, and 2 column level formulas using index/match cross-references to the same (or different) sheets, then my current inbound cells count is 2…
-
COUNTIF Text Cell
Hi All, I am struggling with this formula to count based on a text field search then qualifying with status drop down type. Still getting errors as I know II am missing someything. =COUNTIFS({Project Oversight Range 6}, ([Triversity Construction]@row), {Project Oversight Range 2}, "To Do") + COUNTIFS({Project Oversight…
-
Multiple IF Formulas Based off a Date
Hello Everyone, I'm trying to create a formula with multiple IF statements. So far, I have the following created: =IF([OH Date Qualified]@row < TODAY(-1080), "Designer 2"), IF([OH Date Qualified]@row < TODAY(-1800), "Designer 3"), IF([OH Date Qualified]@row < TODAY(-3600), "Designer 4") However, the above formula only…
-
Retain "Thousands format" when concatenating cells
Hello, I have two cells, "Amount Requested (local)" and "Local Currency Type" that I want to be concatenated in a single cell. I want the amount requested to retain the "," that is added by using "thousands" formatting Here is my current formula, is there anything I can change it to so that it spits out "980,425 Euro"…
-
Countifs projects starting or ending this week in sheet Summary formaulas
Hi all, I need help on a formula to gather how many projects are due to start this week. I have this so far, but it returns 0, but one date range is within the parameters. =COUNTIFS([Target Start Date]:[Target Start Date], WEEKNUMBER(TODAY())) I also need to count on the [Target End date] on how many are due for completion…
-
Formula to return a status based on due date
Hello, I am trying to return a status for projects based on due date. I have a date column for Due Date that has some blanks and another column for the Status. In the Status column I have the following formula: =IF(ISBLANK([Due Date]@row), "", IF(WEEKNUMBER([Due Date]@row) = WEEKNUMBER(TODAY()), "This Week")) This is…
-
I want to update a cell if any of the cells in a certain range of cells equals a certain text.
If ANY cells in Status3- Status7 = In Progress then I want Status2 to equal "In Progress, If ALL cells in Status3-Status7 = Complete then I want status2 to equal "Complete" @Paul Newcome @Andrée Starå