-
Nest IF formula
I have tried looking at the help but it doesn't provide details on creating nested IF formula. I am trying to say: If the column has name 1, then return statement A; if the column has name 2 then return statement B, if neither then return statement C. Any tips?
-
Referenceone sheet and if 0, reference another sheet
Hello, I've been using smartsheets for several years now but really only starting to use formulas until recently so forgive my lack of knowledge. I have three sheets in play here. The primary sheet is looking at the second sheet for an average based on all references of a SKU. The second sheet might not have data for a…
-
@cell Reference for Whole Column
I tried to create a formula with a structure like COUNTIF(column:column, YEAR(@cell)=2021) and was getting an error. I realized that if I changed to COUNTIF(column1:column23, YEAR(@cell)=2021), the formula would work. It seems that the @cell reference does not work when a whole column is selected. Does anyone have a…
-
Average value between two dates.
Hi, I have generated a report from different project sheets. I have a column (C) which has dates when a particular task is completed. Column D calculates the time taken to complete that task (Turn Around Time - TAT). I want to calculate the average TAT taken to complete a task in the month of Feb. I am not able to put two…
-
Counting number of selections made in a multiselect column cell
I have a sheet that we are using to track partners that attend various meetings. The organizers of the groups select the partners that attend a meeting from a multi-select dropdown list. I need to be able to count how many partners are marked in each of these cells individually. For the screen shot below what I would need…
-
SUMIFS
Hi, I'm looking to sum the cells in a column if another corresponding column is either of two values. I'm getting #UNPARSEABLE though! 😕 =SUMIFS({Data Range Spend}, {Data Range Award Type}, OR(@cell = "Direct Award”, @cell = "Mini-Competition”)) Any thoughts welcome! Thanks, Derek
-
Nested If & And error
Hello, I'm relatively new to Smartsheet and am having an issue building a nested if/and function for one of my grids. I have 2 columns ("Effort" & "Impact") and am trying to get 4 unique values based on the numbers - If Effort@row >12.5 and Impact@row<45 show "Kill" If Effort@row>12.5 and Impact@row>45 show "Challenge" If…
-
Setting up an Office Status Indicator
Hello! I am trying to setup a sort of "office status" dashboard for staff. Can anyone guide me on a good way to do this? I am a bit stumped as to how I can get some of the info across. I have one sheet with PTO information: PTO Start (Date) PTO End Date (Date) Currently on PTO (True , False) Name Request Duration (Whole…
-
Formula to set a color of an icon depending on the % brackets
Dear Colleagues, I am trying a formula with dot icons to conditionally set the color if the budget is exceeded by: 0%<10% a dot remains green 10%>15% a dot becomes yellow 15%> a dot becomes red I have tried this formula but my colors are wrong. Please see an example. I suspect that my formula is incorrect because the 0.19%…
-
ADD LAST COMPLETE DATE
I have the following columns that are linked to another smartsheet: Citrix Status - Network Status - PC Status - Phone Status - Completion Date When all columns have the "Complete" status, the Completion Date will register the date of last column that changed to "complete" status. I really appreciate any help! Rob