-
How do I count the number of times a "Staff Person" selected a dropdown option?
In my original sheet there is a form staff can fill out to request passes. I'd like to count the number of time each staff person requested a pass and what pass they requested. In my metrics sheet the "Staff Name" column has all the names and the other columns would hopefully be where number of "Parking Pass"es the staff…
-
Workdays Counter
Looking for help with the below formula. It countdowns the workdays from the start of the promotion to the completion date. The problem is once it goes past the completion date it starts counting backward. I would like it to stop counting once the completion date is hit. Thank you for any assistance.
-
How to list values in a cell based on another cell with distinct names (in a list format too)
Hi, We are trying to present the labor hours workers have dedicated to one same task on different instances. We started by nesting =JOIN(DISTINCT(COLLECT({Worker}, {Task}, "DC6-" + [Task Label]@row), ", "), ", ") We have successfully filtered a list of workers that have dedicated hours to a task. We would then want to…
-
Formula to convert date (e.g., 03/13/22) to text/number field (03/13/22) in same sheet?
A sheet that has a Dynamic View includes a date field (Start Date) that Dynamic Viewers need to use as a filter. Since users cannot filter by date fields in Dynamic View, I've added a helper column (text/number) in the sheet but cannot find a formula to pull the dates in the Start Date column into the helper column so…
-
I want a formula that specifies the lowest child of an ancestor, parent, child hierarchy.
I am using the Hierarchy formula, =COUNT(ANCESTORS()), to label my project tasks. A lot of the child tasks have tasks below that, going to a hierarchy of 5. I want to create metrics sheet that shows the estimated completion date for the lowest level child. Because the levels vary by task, I cannot choose a specific…
-
Average if multiple criteria
I am looking to see if there is a way to have multiple average ifs to show a visual for the current working project So IF status = working, and assigned to Group 4, I am looking to show average # of days: 1.) Submitted to triage 2.) Triage to start 3.) Start to Root cause 4.) Root cause to council approval 5.) Council…
-
Median & Sum of Multiple Columns in Date Range
Hello, I am attempting to write a formula to display the median of 6 columns, only if they contain data within the last 7 days per the date column. I also need to display the sum of the same 6 columns if they contain data within the last 7 days per the date column. I've tried what seems to be an endless variety of formulas…
-
IF, IFERROR, SEARCH nested formula help
Hi team - Looking for help in either fixing the below formula or creating a new formula. Goal behind the logic: Be able to use this cell to confirm if another column,same row (Level) has the abbreviation "Mgr." If it doesn't add a dash "-", full stop. If it does, we need to search another column,same row (rep region) for…
-
SUMIFS Question for adding a criteria for a column sum function
Hello! I am working on creating a travel budget dashboard for my team. I threw in some random numbers but essentially I am creating this data sheet to reference my main sheet where I house all travel budget requests. In my other sheet I treat as a data repository of all requests I receive. The above {cost} and {Dom or Int}…
-
Health Status/Rating calculated by Removing Specific Task names
Hello Smartsheet Community, My team and I are looking to improve a formula that helps to roll-up a score/health status of all of items within an activity plan, which is our RAID Log (Risk, Action, Issue, and Decision). We have a formula that calculates an aggregation/overall rating of all of the risks we have identified,…