-
AverageIf with multiple criterion
I would like to find the Average Resolution time (Days) for rows where the date closed is in June 2023. =AVERAGEIF(AND([Date Closed]:[Date Closed], >DATE(2023, 6, 1), [Date Closed]:[Date Closed], <DATE(2023, 6, 30)), [Resolution Time (Days)]) I'm getting Invalid Data Type. Thank you in advance!! Jennifer
-
COUNTIFS+CONTAINS+AND
I am trying to write a sheet summary formula to count the number of rows where: Sprint Name contains "Maintenance", Date Resolved is not blank, and Bug or Request equals "Bug" [Sprint Name]:[Sprint Name], CONTAINS("Maintenance") and [Date Resolved]:[Date Resolved], <>"" and [Bug or Request]:[Bug or Request], "Bug" I tried…
-
Formula on Children
This is likely an elementary question, but I am having a challenge to get the formula for the Count of Children when I have the formula, for example, in a column B cell and I want to count the children of column A. What am I doing wrong?
-
Formula Help
Hello, I recently used this =SUMIFS formula and now that I have changed some VLOOKUPS the formula gives me a #NO MATCH error. SUMIFS([Request Total]:[Request Total], [Employee ID]:[Employee ID], [Employee ID]@row, Date:Date, <=Date@row, Date:Date, >=DATE(2022, 7, 1), [Department Approval]:[Department Approval], "Approved")…
-
VLOOKUP across Multiple Sheets
I am trying to have a cross reference that looks across 3 different sheets. I have been successful getting it to work with just two sheets, but when i add the third, it comes back "INCORRECT ARGUMENT SET". Any help is appreciated. Here is my formula =IFERROR(VLOOKUP([Employee Email]@row, {Supervisors 1 Range 1}, 2, 0),…
-
How do I create an IF functions that will change my status color if information is in the cell
I am trying to have my event status which is (symbols) update to gray, red, yellow, or green depending on if 2 cells contain information (Green), or one cell or no cells have information and is more than two weeks from the date (yellow), one or no cells have information and is less than two weeks from the date or event is…
-
Cross-sheet Formula Limitations and Possible Work Arounds
First, what does the term "distinct cross-sheet reference" mean? I've just run into a message that I've exceeded the 100 distinct cross-sheet references and am looking for a possible workaround. Background: We have three sheets with various information about our employees. Basically, one sheet is the Employee Master List.…
-
Totaling # the of projects that are older than 30 days, 365 days etc
Goal: how many open requests are older than the specified # of days old, this formula returns a result, just don't think it is the correct one as when I try to duplicate it and ask for requests more than a year old, the number encompasses ALL open projects in the last 365 days not the ones specifically older than a year:…
-
How do you calculate the average number of days between a submitted date and a start date?
Additional context: We use Smartsheet to track support requests from the organization. We have an automated column that records the day the form is filled out. The form also has a date range of when they need their request completed by. I would like to calculate the average amount of days people provide from the day they…
-
Check a box if 3 criteria (checkbox columns) are met
I'm trying to identify registrants for a training who no showed using a checkbox column. I have checkbox columns for the following critieria: Invited Attended Declined I'd like a formula for my No Show column to return a checked box for anyone who: Was invited (box checked) Did not attend (unchecked box) Did not decline…
