-
How to use AVG and COLLECT to average data based on multiple criteria?
Goal: To average the estimated GP data in a column on a reference sheet based on the person assigned to the projects and the completion date being within a specific quarter. For example, I need to know the average estimated GP for all projects assigned to PersonXYZ, for March, April, and May. I have the Average working for…
-
How do I display a different symbol based on multiple values?
I've searched the community and tried multiple formulas but I cannot seem to get this scenario to work. I have a '% Not Achieved' summary field, and depending on the % I want Green, Yellow or Red to display. Scenario: 0% = Green 1% - 10% = Yellow 11% and over = Red This is the formula I'm currently using. I get the Green…
-
Project Intake Sheet looks correct, Project Metadata and Project Plan missing reference
Hello all, I'm new to SmartSheet so still learning. I'm using the PMO template which makes sense so far, as per the instructions. If I understand the functionality right, information feeds through from Project Intake Sheet to the Metadata and Plan sheets in the specific project folder (ie. Project Name feeds through to the…
-
How to combine if statements utilizing information in multiple columns
Hello, I currently have the following formula to automatically have a color coded dot assigned in a column depending on the due date. =IF(ISBLANK([Due Date]@row), "", IF([Due Date]@row - TODAY() > 5, "", IF([Due Date]@row <= TODAY(), "Red", "Yellow"))) Can you help me add to this same formula an if statement that…
-
Count unique projects
I want to count the number of unique projects under a particular workstream. For example, a workstream might have three projects under it, but each of those projects might have three people assigned to it. That's 9 occurrences in that workstream, but only three unique projects. How can I count the unique projects under…
-
Help with SUMIF
Hello, I am hoping to get some guidance on constructing a formula. I am trying to calculate the number of days each person have been allocated for projects. This will help me in understanding monthly/annual labour allocation and available free time. I have made column for each person on the right and entered the results I…
-
How to use Conditional Formatting for entering text when a condition is met?
I am creating Project Plan in Smartsheet. If % Complete = 100% I want Status column to be updated with text = "Complete". If % Complete < 100%, I want Status column to be updated with text = "In Progress". If % Complete = 0% I want Status column to be updated with text = "Not Started".
-
I am trying to create Workflows based on varying frequency and day row is added.
I have a Smartsheet that gets updated with new data every Monday. As soon as a row is added, the project manager gets a notification to set the "Reporting Frequency". This can be Weekly, Bi-Weekly, or Monthly. The first Thursday after the row is added, a Buyer should receive a workflow that requests updates. Then the Buyer…
-
Countifs with criteria on 2 different ranges
hello, I have formulas in a metrics sheet looking at information in a second sheet. I want to count the number of items in the second sheet that meet both a criterion of a checkbox being checked in one column (SS range 2) and the value in another column (SS range 3) being between 2 values (in this case, between 0 and the…
-
VLOOKUP with Multiple Criteria or should use INDEX or COLLECT?
Hi! I'm trying to do a multiple criteria look up and I'm not sure I'm going about it with the correct formula. I have a Project ID referenced on a project profile, then the formula needs to find that project and display the data, easy with a vlookup. Here's where it gets fun, I need it to look at the next unique time entry…