-
Formula to get sheet summary to update percentage complete based on category value
Hello! I am trying to manipulate a formula in my sheet summary to return a percentage complete based on the value selected in the dropdown menu I created. The formula I've tried (and a few variations of it) is: =100 * COUNTIFS([Review Complete]:[Review Complete], 1) / COUNTM([Primary Practice Area]:[Primary Practice Area],…
-
INDEX/MATCH with multiple "Matches"
I'm designing a project plan template and would like to use an INDEX/MATCH formula to populate a "Business Unit" column based on the staff member assigned to a particular task. Doing this would then allow me to create reports for the different Business Unit Mangers (filtered by the Business Unit column) that allow them to…
-
Receiving #invalid value output from a formula
I have a fairly simple ask and I originally built the formula myself, but since the output was an error I ran it through Smartsheet formula generator and our internal company AI. Both gave the same formula. I have a column with Client Names that can begin with either a letter or number. I have a column that needs to output…
-
Formula for counting if cell has RYG symbol
I need to count the total number of cells within a column if they have either Red, Yellow, or Green symbol. We also have the grey symbol, but that should not be counted. I am using this as a way to indicate the total number of active projects in our dashboard. I have tried =COUNTIF(Status:Status, "Green") OR(Status:Status,…
-
Formula not working
I'm trying to add a column that indicates if a line item is urgent or not. Urgent meaning the due date is past or within the next 5 days. The formula I am using is =(cell with due date)<=TODAY(5) I keep getting an error message #INVALUD COLUMN VALUE. My column type is set to text/number.
-
Need to Create Separate Opens List from Data Sheet
Summary: I have a data sheet with a primary column listing different equipment items. Going across, there are a number of columns, 15 of which have drop downs with options N, N/A, Y, and Open Item... I need to populate a list, to a different sheet, showing only the columns, where that piece of equipment shows, Open Item.…
-
COUNTIFS, within a month AND not blank
I'm really struggling with countifs in my Sheet Summary. I added a helper column to sparse the month. I want to count if the created date is February, and if two columns containing IDs are not blank. =COUNTIFS([Month]:[Month], = 2,[Contact ID]:[Contact ID 2],""<>) I'm getting an #UNPARSEABLE error. I tried the formulas as…
-
Sheet Summary Formula Not Pulling All Data
Hello, I have a formula set to count how many projects are flagged within a specific function if they are listed as in progress, but the count is off and I can't figure out why it's not counting all instances. Is it because the column is allowing multiple selections in that column?
-
Formula Issue - Email Addresses
Hello, I am running into an issue of automations and triggers not working because of multiple email addresses in a cell. As you will see in the Dashboard screenshot attached, rows 2 and 3 that have one email address listed under the "College/Department Designee Email" column is leading to all appropriate workflow actions -…
-
Count number of days in date range that fall in current week
Hi everyone, I have a number of project plans in Smartsheet and am trying to get a summary of how many task hours each person has assigned for the current week. The plans currently capture task Start Date, End Date, and number of hours required to complete the task. As some tasks will take multiple weeks to complete, I…
