-
COUNTIFS children based on project name
Hi, I am currently trying to extract data to add to dashboards for synthetics, and a requirement is to know how many sub-projects are in any given area. I am after a way to reference my sheet using a lookup function to return the number of children in each project with a certain column (Task name) The formula must look at…
-
basic formula to auto-populate one cell based on selection in another cell
Hello - Hopefully an easy question, I just have a mental block when it comes to the formulas. I have a "Location" column with six options (single select). I have another column, "Representative" that has 4 selections. I would like the Representative column to auto populate based on what is selected in the Location column.…
-
countIF a value column if the attribute is along side another attribute in a "Multi select Column"
Does anybody know if you can create a formula that will count the values in a “dropdown (Multi-Select) column", when adding summary fields on a Smartsheet list? What is happening now is, if I have Apple and Orange in one field and I want a count for Apple. it will not count it. Only if I have Apple along in a field? If I…
-
Change symbol color based on specific criteria.
I want to change symbol color based on the below criteria. Red- if Status = "Scheduled" and Days open is equal to or greater than 14 Yellow- if Status = "Scheduled" and Days open is between 9 & 14 Green- if Status = "Scheduled" and Days open is less than 10 Gray-If Status = "Complete" I may not need the "status =…
-
Change Time into Military
Hi all - Looking for solution to a simple task. Using a form that shows date in this format: 8:00 AM, 9:30 AM, etc., Want to add a second column (that will be hidden from form) that changes this to military time. No calculations involved - only looking for the time conversion. Yes, we could ask for military time in our…
-
Health of Task or Project using % Complete & Finish Date - Sanity Check
I found something close to what I am looking for here: NEED HELP: Health Formula based on start date, end date, and %100 complete needed. — Smartsheet Community This formula doe s a great job of flagging the dates in relation to time left. However, I am looking for a health check that can take into account both percentage…
-
If then formula with different sheets
Hello, I am looking to create a formula that looks at the Tag Name/ID from these two different sheets. If they are the same, I want the Date Received in Full to show up in the Received by Van Ert column for that Tag Name. For example: The Tag ID in line one on the first sheet is 410-AE-1391B. The Tag Name in line 581 on…
-
Countifs for a range based on checked box in a different column
I am working on a training matrix where our team tracks the operator's training status for multiple assays. We also have a columns for different suites of trainings needed per job title where if the assay is needed for the job title, the box is checked. The ultimate request is to show % Complete Trained based on the…
-
Combining AVERAGEIF and AND Functions
Hi All, I am wondering whether it's possible to combine the AVERAGEIF and AND functions together to meet 2 criteria. In my case, I'm averaging time - {X} is in minutes hence the division by 60 to convert to hours and the 2 to round to two decimal points. The below works as expected. =ROUND(AVERAGEIF({X}, {X} > 0) / 60, 2)…
-
Join/Match/Count Criteria
I have a sheet that is populated by another sheet. And in this populated sheet - I'd like to count how many times certain criteria show up (two specific data points). I assume I would need to join the two columns (i.e., Last Name and ID) and then if these two items show up together again, I'd like to have a count of these…