-
Date use with COUNTIFS formula (Non-Specific Date)
I have set up a summary grid pulling information from another Support calls grid so I can keep track of logged calls completed by members of my team. I am currently using the formula below to pull that information for one of the team and it works fine. =COUNTIFS({Support calls Range 1}, "Complete", {Support calls Range 2},…
-
Status RGYB balls formula, when due tomorrow and past due
I built out this formula to change the status marker balls colors based on today's date and my "due Date" column: =IF(Status@row <> "Complete", IF([Completion/ Posting Date]@row > TODAY(10), "Blue", IF([Completion/ Posting Date]@row > TODAY(5), "Green", IF([Completion/ Posting Date]@row > TODAY(2), "Yellow",…
-
join collect several variables
From a metrics sheet I am looking at another sheet with a formula which gathers the docket number if the date is before today and it returns the result perfectly. 12234, 67899, 98765 =JOIN(COLLECT({Docket}, {DueDate}, <TODAY()), ", ") I am trying to modify this formula to return 2 variables (docket + Client) when date is…
-
How do I make cell references in formulas dynamic in Smartsheet?
Cell A must be the average of cells D11 and E11 of the different SUCRIVOIRE-BOROTOU-KORO and SUCRIVOIRE-ZUENOULA sheets. So within A we have the formula = AVG ({reference of D11}; {reference of E11}). How to ensure that by copying or duplicating the formula from cell A into cells B and C, on the SUCRIVOIRE sheet, we…
-
Create multi-contact cell from a row array of individual contacts
I have a sheet as: Header: SME1; SME2; SME3; All SMEs Row 1: Bill Ross; Suzie Collins; Anne Doe; =join([SME1:SME3]@row) Each of the SME columns is a contact. I want to fill my 'All SMEs' column with a formula which makes a multi-contact with all of the contacts joined together for easier filtering. I've tried to write the…
-
Extracting text from a column to find combine average
Hello and thank you for your help! I have three "select one" dropdown columns that have rubric scores for questions, e.g. 1 - no applicable experience 2 - minimal experience 3 - some ability 4 - above average 5 - high level Is there a formula to just extract the score (or number) selected in each column and find the…
-
Update request - add field from other sheet
Hi, is there possible to add fields from other sheet (i.e. sheet B) to an update request from a sheet A?
-
Excluding 'NA' from my KPI formula
Hi Smartsheet Community, I am trying to exclude 'NA' from my KPI formula =IF(AND([Days between Submission/Approval]@row > 28, [Trial Type]@row = "Oncology"), 1, IF(AND([Days between Activation/1st Consent]@row > 7, [Trial Type]@row = "Healthy Volunteers"), 1, IF(AND([Days between Activation/1st Consent]@row > 28, [Trial…
-
SUMIFS Referencing another Sheet
Good Evening, Im currently stuck and trying not to bang my head against a wall. im currently trying to sum the total number of hours based upon 2 sets of criteria. One of the criteria has a name, and the other is within a date range (such as January). I currently have below and it sends back a 0 on the calculation, any…
-
Formula help for countifs with multiple criteria
Hello, I would really appreciate some help crafting a formula for a dashboard. Thank you in advance for your help! Goal: count based on current year Criteria: field type (lead time) within the current year (request date) referencing a different worksheet Formula attempt: =COUNTIFS({Lead Time}, HAS(@cell, Type@row),…