-
How to keep a list in order when drawing from a dynamic source
Hi all, I have a list of projects in a status update sheet. This list is automatically updated from a sheet that receives updates from a JIRA to Smartsheet connection. My issue is that the list of projects is in a priority order (with a seperate column to give a priority number), but sometimes when the JIRA pull sheet is…
-
Include Hyperlink in Formula where I'm pulling through the parent task?
Hello! Im my sheet I have a formula in a helper column to pull the value of the parent task into each row - our parent task is our project deliverable name and it helps to have that in line for other views / reports. In our parent tasks, we are hyperlinking to our project brief. However, that hyperlink does not pull…
-
SUMIF plus COUNTIF
Hi all, I asked another question in this thread. I now want to take a step further. I would like for the formula to sum the amounts in the SAR Loss column of my other sheet only if the Date Filed is between <Date 1> and <Date 2> AND the subcategory type matches @row. =SUMIFS({Date SAR Filed}, >=DATE(2024, 1, 1), {Date SAR…
-
Percent Complete of Status Column
I am looking to have the Parent row show the sum of percent complete for children in the status column. Based on the photo attached, the formula I am using is not calculating the "Activity 1" cells being marked complete. I am using the formula: =COUNTIFS(CHILDREN([Status]@row), "Complete") / COUNT(CHILDREN([Status]@row))
-
Removing Duplicates from a Report
Hello, I have a sheet with many line items, some of which share some columns that have duplicitous information. I would like to pull a report from that sheet to display certain information on a dashboard and, ideally, within the report, run a conditional format to remove the duplicate line items. I am also reading up on…
-
Help with Formula
Hi Community, I am trying to calculate a formula that references a date column 1 (Start Date), and a date column 2 (transfer Date) and chooses the date column that is greater, if there is no date column 2(transfer) present then it defers the date column 2. Any assistance is greatly appreciated Thank you,
-
Counting only weekdays Formula?
Hello, I have a ticket tracker where it counts how many days a ticket has been open. Currently it includes weekends and i don't need it to. Is there a way or formula to count the amount of days a ticket has been open excluding weekends? I have a date created column and then a date closed column and use this formula current…
-
COUNTIFS/CONTAINS FUNCTION
Hi All, I need help with a formula to Count every instance a name appears in a multi-select row of a column based on criteria from another column first. At present this is my first formula: =COUNTIFS({CORP ADMIN TEAM TASKS Range 1}, "In Progress", {CORP ADMIN TEAM TASKS Range 2}, "Jane Doe") However, it is only…
-
Cross Sheet Countifs multiple columns
I’ve built out an Error Tracking Mastersheet and I’m now trying to roll the metrics up but I’m struggling a bit. There are 4 types of errors that can be made (DE, CE, LE, EE). There are close to 20 people, but for the example below I only show 3. Each person is their own Drop down column with the same 4 error types. Each…
-
COUNTIFS Function of # of Values Used in a Median Calculation for Metric Sheet
I have a formula in a metrics sheet where I am successfully calculating the median of values that meet a specific date range: =MEDIAN(COLLECT({Resi - Financial Approval Duration}, {Resi - Enter Stage 7 Date}, AND(@cell >= $[Start Date]$1, @cell <= $[End Date]$1))) I now want to calculate in a adjacent column the total…
