-
Sumifs, Constains, and Dates
I'm trying to calculate the average time it takes to close a ticket. I know that I want to use SUMIFS/COUNTIFS. The COUNTIFS formula works fine: =COUNTIFS([Initiative/Program]:[Initiative/Program], CONTAINS("Metrics/Reporting", @cell), [Start/Raised]:[Start/Raised], AND(@cell <= TODAY(), @cell > TODAY(-30))) I'm struggling…
-
Identifying a ticked checkbox in an =IF formula for a Health column
I have the following formula so that if an Account column checkbox is checked the health status (which are symbols) column is blue. And I want the health to show Red, Green, or Yellow if the Account column checkbox is not checked by varying degrees of days before the price sheet due date. I put this in, but it says…
-
Stop counting days when a row is approved
Hello all!! Im hoping to get some help with calculating a day count for my sheet. Currently, we have "=TODAY() - [Date Received]@row," in the "Days in queue," column which counts the days from when we receive an action to the current date, but we would like to add a formula to where the count stops once the "Approval Sent"…
-
Previous Weeks Formula
Hello! I need to develop a formula to count the number of opportunities awarded this week and then compare to previous weeks. I have a helper column on my source sheet to list the week number for the awarded date. For Awarded Opportunities This Week the following formula is working: =COUNTIFS({Opportunity Intake study…
-
How can I pull all projects associated with one team member into separate rows?
I need a report that shows all projects each employee is associated with under the Team Member column from a multi-select list. Because of the multi-select, I can't run a report and group by Team Member. My work-around was to create a separate sheet that would just pull every project name associated with the employee on a…
-
IF(AND Nested Formulas
I need to each row to display a 'Score' based on results from: Likelihood of Risk - Likely, Quite Likely, Unlikely Impact of Risk - Minor, Visible, Interruption I am receiving the following message with the below formula - #UNPARSEABLE Any ideas? =IF(AND([Likelihood of Risk-Likely, Quite Likely, Unlikely]@row="U",[Impact…
-
Average formula for text field
Hi I’m new to smartsheet I am trying to input a formula into the sheet summary field that will average “closed complete” and “closed cancelled” for a column labeled “status” I was able to count the two fields for both but not average them with the proper formula thank you
-
COUNTIFS and CONTAINS
Hi! I need some guidance for a count analysis of our RAID Log (Risks, Issues, Decision). In the RAID Log source sheet, we have a column where the team selects the Project(s) the risk or issue is tied to. The Project column is set up as multi-select. In a separate Count RAID sheet, I'm using this formula: =COUNTIFS({Risk &…
-
Rollup of contacts into a single cell as a single select or comma sep string?
I have a project plan with 100 tasks. Each task can have one or more assignees in the Assigned To column. I want to have a rollup of all possible assignees into a single cell for project filtering (as opposed to task filtering). I understand i can not do a formula with multiple contacts. I am OK to translate this into a…
-
Yesterday's Data and Rolling 7 Day Avg Data
Hi - I am all-new to Smartsheet and have hunted high and low for the answer to these questions.....with no joy. When I think I am onto something the formula always doesn't work, so i thought i would ask directly... I have a sheet that we update daily and want to create a summary at the bottom with two bits of info. This is…