-
Looking to count the number of times a string of specific text occurs in a column
I want to use a count formula to determine the number of times a SKU occurs in an open text column. Below is the formula for the highlighted cell: =COUNTIF(SKU:SKU, CONTAINS("SKU2", @cell)) The value I am looking for is 3 because SKU2 appears 2 times in the 3rd row and once in the 2nd row. Any insights on how to count…
-
Smartsheet Form - Latest entry is the only "actively" reported
Hi, I have a form for people to fill in that reports how ready they are for security. As this is evolving they can be expected to fill this in from time to time to measure how ready they are. So I only want the latest line from the form to be reported on. My system in place for this at the moment is to have a "Constant"…
-
AVERAGEIF with multiple criteria
Hello, I am trying to find the Average calculated time of a column(Avg Time) when it is a specific Status and Px Code. For Example, The average time code '1234' is in status 'Approved.' I've tried these two formulas: =AVERAGEIF({Avg Time}, AND({Px}, Px@row, {Status}, Status@row)) =AVERAGEIF(AND({Px}, Px@row, {Status},…
-
Alert workflow when item is in a given status for 10 days
Hi there -- I have a use case where I need to send a specific person an alert if a row's Status column has remained in the "Upcoming project" status for 10 days. Likewise, I need to send this same person an alert when a row's Status column has remained in the "v1 to ESG analyst" status for 10 days. I think I need to use a…
-
How to return a blank cell in my formula results when the referenced cell is blank?
I've been trying various different ways to add to my formula to return a blank cell when the date cell I'm referencing is blank, none seem to be working. The formula should give me a 3 if the date is 0 - 90 days in the future, a 4 if between 90 - 120 days in the future, a 6 if 120 - 180 days and "over 6 months" if more…
-
Automating Time Spent
Hello, I've been reading through various articles, threads, and Google searches looking to see if there was a solution to calcuate how long it takes to complete a task. Currently, I have a sheet user inputing a new row and the first portion of data, lets just say columns A:D. Then, they save and work in another system.…
-
SUMIF stopped working
This formula was working in my sheet (summary field) and it just stopped. I don't know what I could have changed to make this stop working. I am now getting an invalid data type error. It's a simple formula! If the year is 2022, sum up the charges column. My Year column is calculated, YEAR([Date Signed]), is that why? I…
-
Year Support Contracts - Automation to update end date
We use the following formula to populate the end date of a one year contract. Namely if it starts the 1st Oct 2022 the contract expires on the 30th Sept 2023. This formula works for every other day in the month except when the renewal falls on the 1st ? =IFERROR(DATE(YEAR([Support Contract Start Date]@row) + 1,…
-
How to include Count Collect formula with Parent Row Count Formula?
Hey there, I'm trying to add a 'Count Parent and other rows' formula to my existing count collect formula. See existing formula below. =COUNT(COLLECT({Row ID}, {Category}, $Label@row)) It is counting specific fields from the intake sheet and placing in a newly created sheet. To update it I want to exclude child rows from…
-
Can you create a column formula for auto-number generation that will recognize ancestor relationship
I have a sheet that I would like to custom create an auto-number formula so a record ID is given when a new record is submitted, but I also also want it to be able to recognize descendant rows and give them the same Record ID as the ancestor row (instead of generating a new record ID for every new row). Below is an example…
