-
Sumifs Formula with a Contain
I am trying to get the sum of hours worked IF the "Office" Column = Administrative Solutions. The Office column is a multi-select dropdown. =SUMIFS({Project Intake with Form Range 1}, {Project Management Tracker- Discovery to C Range 9}, CONTAINS("Administrative Solutions", @cell")) Any help would be appreciated! Thank you!
-
Formula to Show At-Risk Flag
Does anyone know how I might modify the results portion of this formula to flip a flag to red? =IF(AND(Finish@row <= TODAY(3), [% Complete]@row < 1), "1", "0") Figured it out! See below... =IF(AND(Finish@row <= TODAY(3), [% Complete]@row < 1), 1, 0)
-
Formula help
Hello- I'm trying to count number of times text shows in a column and it be a rolling qtr. the column name: "Management code key" and i want to count the number of times "communication" is mentioned. below is my current formula that I'm getting the #unparseable error. formula works without the rolling formula but it counts…
-
How to convert CAGR formula(end/start)^(1/period)-1)?
I am working with an excel sheet and trying to move it into the smartsheet platform. Having a hard time getting the correct formula for 2021 CAGR looking at the 2019 actual.
-
View Only Access to Subset of Columns
I'm looking for way to allow a restricted view (sub set of columns) to and an external organization (I've set up a View Only user group). 1st try: Create a report with just the columns - FAIL. I still need to grant the view only permission to the source sheet 2nd: Create a 'query sheet' that 'VLOOKUPS' just the columns -…
-
Check Mark Row
Question- I have 3 check marks within a row. My goal is to figure out a way to have only one of those check marks "checked" at any given time, not multiple. So if one is checked in one column and i check another box in another column then it will remove the existing check mark in the other column. Anyone?
-
COUNTIF WITH CONTAINS/ AND/OR
=COUNTIFS({TABLE TYPES OF APPLE Range 2}, CONTAINS("apples", @cell), {TABLE TYPES OF APPLE Range 1}, OR(CONTAINS(@cell, "red"), CONTAINS(@cell, "green"), CONTAINS(@cell, "yellow"))) My formula does not seem to work. I would like to countif if it is apples in range 2 and range 1 contains red, or green, or yellow. In…
-
Dashboard Metric sheet formulas
I am new to Smartsheet and have been tasked to make a Dashboard that feeds from a large spreadsheet. I have created a metrics sheet and for the most part, it works well for the dashboard, but I haven't been able to solve the following issues; 1) How do I make the metrics sheet update when a new entry is made in the main…
-
AVG(Collect formula with multiple criteria
Hello: Im trying to figure it out how to calculate the average "Days to Closed" for each "Task Owner" in 2021 ("Year"column ). I was trying Avg(collect formula. But i get #invalid ref error. The formulas i try : =AVG(COLLECT({Days to Closed}, {Task Owner}, "Chris Jularbal", {Year}, IFERROR(YEAR(@cell), 0) = 2021)) and…
-
Sending 2 different alerts based on start date
I have a Smartsheet where I am capturing information about new employees. I need to send an alert to these new employees on day 3 and day 7 after hire. Can I send two alert from the start date column or is there a formula that I can use to create these date rows and have an alert go out on that particular day?