-
If formula with nested AND, OR, NOT ISBLANK within
Not sure what I did wrong as when I'm reading through the formulat i wrote it seems correct, but I'm not getting the correct color value. Essentially I need: if the status is "Not Started" OR "In Process" OR blank AND the "End Date" is not blank OR is less than today's date, turn red. If the "End Date" is blank=Gray",…
-
CountIF Formula Help for Dashboard
See screenshot. I want to create a graph on a dashboard that shows how many total rows on the sheet pertain to Medical, Fire, and/or Police (indicated by checkbox columns on each row). Will I need to create an Agency Name sub row for each Medical, Fire, and/or Police I want to be represented on the graph for the COUNTIF…
-
Searching formula for index/match, searching for matching value on multiple sheets
Hello all, I'm a newbie and am getting stuck trying to find the correct formula to return an ID associated with a person when searching for this person across multiple sheets. I'm familiar with INDEX/MATCH, but am having trouble figuring out what function to combine with it. Example: Homer has a record in Sheet A…
-
Average IF or Avg(Collect) to obtain the average of multiple scoring values
Hello Everyone... I am a bit new to Smartsheet and trying to find my way. I have an issue where I have an external reference to a 2nd sheet to collect a point total for how thoroughly some questions are being answered. I have up to 8 different people who will be scoring the responses and these scores will be tied to the…
-
Repeating # formula
this is what i have - but not working IF([row #]18, =1, "2", OR(IF([row #]18, =2, "3", OR(IF([row #]18, =3, "4", OR(IF([row #]18, =4, "5", OR(IF([row #]18, =5, "6", OR(IF([row #]18, =6, "7", OR(IF([row #]18, =7, "8", OR(IF([row #]18, =8, "9", OR(IF([row #]18, =9, "1", i want if the row above is a 1, the next row to equal 2…
-
Formulas -Automated at risk status
Hi all, I'm quite new and I'm struggling with a formula I need to apply. I need to have an automated "at risk" status (red, yellow, green) in my projects masterlist. Red: % Done is equal to 0% and the Start Date has passed Yellow: % Done is bigger than 1% Green: % Done is 100% the formula I'm coming is this: =IF([% Done]1…
-
Calculating SUM based of criteria from multiselect.
Good day, I have a form with quite a few multi selects. After a user selects all that apply, I want to cross reference what he selected with a second sheet that has "costs values" associated to the same text that the user had available to him in the multi select drop down on the form. So basically after the user fills out…
-
Using CountIF function to calculate Count of Market
Hi! I'm looking to get some help on a CountIF formula. I have a sheet "Territory 4 Exit Interview Pivot Table" which I am looking to get a count of how many employees are in Market 41, 42, 43, and 44 (from the T4 Exit Interview Results sheet screenshot). I keep getting all sorts of errors when trying to use the CountIF…
-
Incorrect field value in email with Smartsheet automation
Hi, I see an issue in automated email if we use a Date column with formulas. In my sheet, I have a Date type column (CreatedDateTime) with formula =Created@row+"". So, When a row is added, this cell shows the Date and time based on timezone selected in Account->Personal Settings. e.g. 06/23/21 6:27 PM I am in GMT+5:30…
-
What value does MATCH return if it matches nothing?
Or possibly my question is what is the null value in Smartsheet. And does it matter if the context is boolean or not? Thanks.