-
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…
-
How do I count by year based off a condition?
Hello, I'm trying to count how many dates in each state per year (snapshot below). I've tried this formula but it's not counting correctly: =COUNTIFS({State/country}, @cell = "NSW", {Date 6th Day was worked:}, AND(IFERROR(YEAR(@cell), 0) - 2021)) What do I need to change?
-
Month to Date and Year to Date spending
I have a sheet which contains date and amount spent, among other things. I want to show a MTD and a YTD metric on a dashboard. I am assuming I need to make a new sheet with a formula pulling data from the original sheet. I would like this to be ongoing as the original sheet is updated and then at the start of a new…