-
Trying to use COUNTIFS to count Items in a drop-down column
I have a drop-down column for Issues and a column for Campuses, and I'm trying to count the number of times an issue shows up for each campus. The formula that I'm using is counting the issues, but only if it is the only item selected in the drop-down: =COUNTIFS({Campus}, "Chico", {Issue}, "Unilateral Change") Instead of…
-
Can Smartsheet automatically highlight / notify if a reference appears on a sheet more than twice?
I am literally new and learning Smartsheet and have set up a customer order form but each customer can only have a maximum of 2 free orders so i was wondering if it is possible to automatically flag if the same reference appears a third time? At the moment i am exporting to Excel and using a countif function to check for…
-
Apply a formula in a Status column only for the Parent rows.
I've been struggling with this a little bit. I want to pick the status with the dropdown options only for the rows without children and pick the status of the parent rows based on that selection. I have this formula: =IF(Ancestors@row = "True", IF(COUNTIF(CHILDREN(), "Not Started") = COUNT(CHILDREN()), "Not Started",…
-
Project Health formula
I want to create a formula that accomplishes the following: If [Target End Date] is today or later or if [Actual End Date] is on or before target end date = Green If [Target End Date] is 1-3 days ago and [Actual End Date] is blank or [Actual End Date] is 1-3 days later than [Target End Date] = Yellow If [Target End Date]…
-
Calculating daily and last 30 days averages based on form submissions
Hello, I am collecting daily form submissions and want to have a dashboard to report on key metrics from these submissions. See screenshot. Here's what I want to calculate: Daily average for columns Role, Identity, Technique, Quality of Day, and My Score Today. At the end of the day I need to see what TODAY's average…
-
Multiple if statements
I am receiving an unparseable error and I don't understand why. I have two different if statements. They work individually. =IF([Working days until due date]@row > 0; "Red") =IF([Working days until due date]@row < 0; "Green") But when I combine them it won't work: =IF([Working days until due date]@row > 0; "Red");…
-
COUNTIFS with Previous date or date to come
Hi, I'm having difficulties with the COUNTIFS formulas and dates. I have a table from another sheet in which I am interested in four columns (Track, Type, Due Date and Done). I would like to know the number of rows where track = "customer", type = "track action", due date = is greater than or equal to ten days from today…
-
Column Formula Question
Is it possible to convert a cell formula to a column formula if it references a different sheet? Here is the formula in question: =VLOOKUP([Project Name]@row, {AM Master Info List Range 2}, 5, false) Where [Project Name] is a column in the current sheet, and {AM Master Info List Range 2} is a range from a different sheet.…
-
I want to calculate a Due Date based on a defined Frequency.
Audit frequencies are Annually, Biennially or Triennially. I have used the following formula but I get the #UNPARSEABLE output. =IF([Audit Frequency]@row=Biennially, "[Last Audit Date]@row+730", IF([Audit Frequency]@row=Triennially, "[Last Audit Date]@row+1095", IF([Audit Frequency]@row=Annually, "[Last Audit…
-
Reference Formula Across Sheets
Hello, I have been working with this formula below in order to search multiple sheets to see if a name has been submitted before. The formula is currently in a checkbox format and I keep getting the error "Boolean Expected". I think it wants to display a number but what I want it to do is check the box if it has appeared…
