-
CountIF one item is a part of multiple selections
Greetings- Im trying to do metrics and a graph for individual items that may be selected as part of multiple items in 2023. For example, my form has the option to select one or multiple from the list: Kids (5-12) Teens (13-17) Adults (18-64) Seniors (65+) Families General Public Staff Use So a user could select Kids and…
-
INDEX MATCH is returning #NO MATCH or the wrong email address
I have a Smartsheet that I have populated with INDEX MATCH, and it works great. My last column is to pull in the email addresses of all the employees, based on their employee ID that has previously loaded into the Smartsheet via an INDEX MATCH. So my [Site Contact 1 ID\@row is data sourced from an INDEX MATCH already. I am…
-
Hi all, need help with a formula to flag the at risk column.
Hi all, looking for help with a formula the will flag the cell in the At Risk column if the "QC Date Completed" is greater then the "Internal QC" or if "QC Date Completed" is blank and today is past the "Internal QC"
-
COUNTIFS multiple sheets but search if CONTAINS assigned to name
I am trying to count the number of tasks assigned to an employee across multiple sheets and if the task is not marked "complete". This formula works only if the employee is the only person assigned to a task. I want it to include any their name appears with multiple employees in the assigned to cell. (So if the cell…
-
Formula to look at specific column/cell depending on the current month
Hello, I want to create a dashboard widget that will show employee FTEs for the current month. I currently have a sheet (Capacity Plan) that list out the employee's name, and then has the assigned projects with FTE breakdown as child rows underneath. Then I have row that calculates the total for the month. For the widget,…
-
Formula to concatenate multiple "tab" characters doens't work as expected
I created a formula to try to create an indented structure for reports by concatenating multiple "tab' characters based on the indent level. However, the formula doesn't work as expected. Instead of ending up with multiple "tab" characters, the output is either an empty string (when lvl=0 or lvl=1) or it's the number zero…
-
What wrong with this IF OR Loop statement
=IF(OR([Site]@row= "Kelowna", [Site]@row="Vancouver", [Site]@row="Victoria"), "British Columbia", IF(OR([Site]@row= "Halifax", [Site]@row="Moncton", [Site]@row="St Johns", [Site]@row="Grand Montreal"), "Eastern", IF(OR[Site]@row= "Central", [Site]@row="Tannis", [Site]@row="SW Ontario", [Site]@row="Toronto") , "Ontario",…
-
Attempting to get sum of hours assigned to a particular contact
I'm trying to get a formula working that will do the following: Parse 3 columns of contacts and for each time that contact is listed, start adding the hours that are assigned to that contact. Each row is considered a different project. I used this formula as a test for just one column (I'm a formula rookie). Note, the…
-
Looking for percentage, getting incorrect arguement
I am sure there is some simple answer, but I brain hurts. We are developing calculations and metrics to quickly assess the status of a project. I am trying to find the percentage of Critical Path taskes up to a Go Live date, that are marked at risk. My first attempt worked, but I received a 'Divide by Zero' error. Now I am…
-
sum total of items when due date exceeds a certain date
Hello! I am trying to create a formula that will sum total a quantity at row if the finish date at that same row is greater than a certain date. In the screenshot the quantity to be totaled is in the SCO Spec column if the finish date on that same row is greater than "06/30/2023".
