-
Find Average Number if Criteria is Met
I need to determine the AVG number from a list of numbers if the Year and Week Number criteria are met. For example, if the Year is 2025 (Year Column) and the Week Number is 1 (Week Number Column), I need the average number of days in the FO Node Upgrades Duration column. I know that this formula =COUNTIFS({Year}, "2025",…
-
Extract date from the string (text)
Can anyone recommend a formula to extract date from the string of any length? Example: "Property: Maple, Ltd Balance Due 1/1/2022" or "12/1/2021 Balance due for Napa" . The string could be of any length and the date could be placed anywhere within the text. But it will always have two "/" characters. Unfortunately, date…
-
IF statement that can pull data from more than one cell if
We are using Smartsheet as a ticketing system for reporting guest issues. I have set up a form for our team to use where they can select the "primary issue category", and then based on the issue they choose, it gives them another drop down where they can choose a "secondary issue" where they can be more specific about what…
-
Update parent value if all children are a specific value, leave alone otherwise
I'd like to update the main parent row cell to "Complete" if all the children are also set to "Complete". Otherwise, I'd like the parent row cell to just be left alone (it will be blank until everything else is "Complete)". I can make this happen based on the other statuses the children show by using a bunch of COUNTIFS…
-
End of Month Function
Good afternoon all, I'm trying to figure out how to apply the =EOMONTH(A1,0) from excel into a smartsheet. Any guidance on this would be most appreciated! Thanks!
-
Formula cells are breaking because the cells are linking to one another inside the sheet
I built out a metric sheet and use sheet references within the cell formula. It was all linking and working correctly. I noticed last week that one of the formulas was not correctly calculating the total as it was previously. When I try to fix the formula and reference the correct column from the other sheet it breaks…
-
Newbie: Using COUNTIF to calculate uses of a text string on another sheet
I think I've reached the limits of my self-teaching and could use a little guidance. I have a sheet called Assigned Categories that includes a Category column. That column is a multi-select drop down list and we've made multiple selections in each row. I have a separate sheet called Statistics with two columns: Name and…
-
Reporting Earned Value vs Planned Value for SPI Metrics
Good afternoon everyone, I'm new to the SmartSheet community, but my company and I have been integrating SS into our PM work processes for about 1.5 years. We're a VOSB DOD contractor, and we use SS to create simple project schedules that include start/end dates, duration, dependencies, and % complete. We do not resource…
-
IF AND Statement for Color Statuses
I am trying to do something that I thought was fairly simple, but I keep running into an issue and receive an "#Unparceable" with my current formula. I am trying to build a Task Status based on Red, Yellow, Blue, and Green Circles. My challenge is getting the proper formula/syntax for my Yellow Status. =IF([Status]1 = ???,…
-
Removing special characters
Hello! I am importing from excel and ending up with extra characters in the cell. For example, instead of the cell displaying: Spanish, Portugues I am instead seeing: ["Spanish","Portugues"] Is there a formula that would remove the [ ] " from the cell? Thank you!
