-
Average Collect Formula Not Calculating Correctly.
Hello, I am looking to determine the average time based based on start/ completion dates, location and the frequency in which the activity occurs. However, it does not seem to be calculating based on my criteria. Could anyone help me determine why the average is not calculating based on my criteria?…
-
If nesting... in a true/false - FOUND IT! Remove the 0 in the nesting
I can't figure out why combing these statements isn't working. This works: =IF(AND(CONTAINS("Director", [Approvals Needed]@row), CONTAINS("Lead or Supervisor", [Approvals Needed]@row), CONTAINS("Manager", [Approvals Needed]@row), [Lead or Supervisor Approval]@row = "Approved", [Manager Approval]@row = "Approved"), 1, 0)…
-
Need help refining a formula where my outcome keeps coming out to 0
For one of my formulas, I am using three columns; a date column (Week Begin) , name column, and number column (total inbound). I am trying to sum certain values in the total inbound column based on the name associated with the data and date range (one week). I am using the formula: =SUMIFS([Total Inbound]:[Total Inbound],…
-
Last 7 days
I am trying to create a formula that will pull data from two columns in the sheet as follows: Sheet columns include intakes by location and a date entered column. I want to create a roll up at the top to show intakes by location in the last 7 days that automatically updates. Here is what I have:…
-
Help | IF/AND Formula for Automating RYG Based on Due Date and Whether Task is Complete
Hello - I have a project plan that I am trying to populate R/Y/G status that uses two criteria: 1) is the task marked completed, and, 2) due date. If the task is due by one day or more AND is not complete, = red If the task is due today and is within 5 business days AND not complete, = yellow If the task is marked complete…
-
Please Help SUMIF Question with Parents and Children
I'm working on a project sheet with dozens of Parent rows, each with a various amount of Children, ranging from one to over twenty. I have a user inputted column to assign known work hours needed for each Child's job, and am trying to create an independent running total for each Parent/Child group. This will allow me to…
-
Automate Daily Clearing of certain column in a sheet
I was wondering if it is possible to automatically clear certain columns in a sheet based on a date or change trigger. We are using Smartsheet as a parts database and after filling out the Quantity, Job# ect. and checking a box the row is added to another sheet to be approved and ordered. I would like to clear these…
-
VLOOKUP returning wrong value - solved
I had the following post ready to go, but figured out the problem. I'll still post to help anyone else. Essentially, when the VLOOKUP final parameter is left to default (true), Smartsheet thinks that "Low" is close enough to "High" to return the value for "High". When I change the final parameter to false, it returns the…
-
Count Cells Based upon Criteria – Between Two Dates
Here is my problem: I have a sheet that has a status of resolved and I want to count the number of resolved issues based upon a specific date range. I know it is a countifs statement but I'm having issues with the correct format Any help would be greatly appreciated.
-
Nested IF/OR Contains Function
Hi, I'm trying to write a CONTAINS formula to auto-populate the project location column. There are 6 states to choose from. The Project Name is usually very long & descriptive but always contains the standard 2 letter code for the six states that the projects could be in. I came across the CONTAINS formula but cannot…
