-
Percentage formula based on a checkbox.
Any suggestions on how to make this exceptions formula more accurate? Smartsheets keeps adding 10 rows to my sheet with no data causing my formula to be off. I have 88 rows with data and a total of 98 rows. Out of the 88 row 21 are an exception with the checkbox selected. This is the formula I am using. =COUNTIF({Master…
-
Formula Question
I have a list of items for sale in (column A) in a dropdown list to be used in a webform.... IE: Dress $25 Pants $50 Shirt $30 I want to have the total cost of the selected item (single select in the column) populated (dollar amount only) in Column B automatically. If Item 1 (Dress $25) is selected in Column A THEN "$25"…
-
Nesting If Statements - Looking for Results Across Two Sheets to Pull into a Third
I am working with three sheets for this project: Sheet 1 - Open Projects, Sheet 2 - Finished Projects, Sheet 3 - All Info. When a project moves from "Open" to "Finished", it is moved automatically from sheet 1 to 2 (these are raw data sheets that cannot be changed or combined). I need to pull down the status into Sheet 3…
-
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…
-
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",…
-
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…
-
Average Collect Issues
Hello! I'm trying to write a formula that will average the amount of time a type of request takes to complete by month. I'm referencing another sheet in this formula and just can't get it to work. Here's what I have so far: =AVG(COLLECT{Hours to Complete}, ISNUMBER(@cell), {Request Type}, "Quote Request", {Submitted Date},…
