-
Count if NOT BLANK
Hi. I am trying to write a formula with multiple criteria to for my metrics sheets; however, I'm not sure how to add this one count if not blank. This is my current formula: =IFERROR(COUNTIFS({OHCA Form - Agency}, $Agency@row, {OHCA Form - Review Complete}, "Medical Review Complete", {2020 OHCA Form - Etiology},…
-
Automatic indentation
Hi, I am using Data Shuttle to bring data from an external source into my SmartSheet. I want to automatically indent based on the Unique ID as shown in the below example. The Unique ID with the lowest suffix should be the parent row and subsequent ones should be children. Is this possible to achieve with a formula or some…
-
Countif with Min and Max Date Range
So the excel formula I'm trying to replicate is below. Basically, I'm trying to count every cell that has a date between Jan. 1, 2021 and Jan. 31, 2021. The outcome should only be one number. =COUNTIFS(Sheet1!N:N,">=1/1/2021",Sheet1!N:N,"<=1/31/2021") But I've tried so many things. I can't figure out what try next. I've…
-
IF AND formula using ANCESTOR
Hello! I am trying to automatically check a checkbox to a cell based on these conditions: The task's parent is "Releases" (task column is named Category) Status = green (RYGB symbol). Here's where I am and it's stating it is unparsable: =IF(AND([Status]@row ="green", PARENT([Category]@row="Releases"),1,0)) Any help is…
-
Reference a contact in IF formula
I'm trying to return a value of "TRUE" in a tick box column if a contact referenced in the same row is a specific person. It's a multiple contacts column too but I've done dummy runs with only one contact and it still won't work. I've tried the following and they don't work, what am I doing wrong? 😞 =IF(CONTAINS("Joe…
-
Formula Error
Please can you Help I'm waiting to count the Outstanding days from the Due Date to Todays Date. I get this formula right but, if the Row Status has be closed or Resolved I don't want it to count anything. My Formula looks like this Would Highly appreciate you help. Thank you
-
Trouble with AVERAGEIF
Good day! Trying to show the completion average for an individual and thought this would work but I must be using it wrong. My reference column calculates the completion average per project, which have has some blanks, and I want the overall average of those per person. Here is what I tried, and failed. Am I on the right…
-
Countif for a multiselect column
Hello, I have a multi select column with numbers in one sheet. I have another summary sheet where I am counting how many times all the numbers have appeared in the first sheet. The countif formula is giving the wrong answer for the numbers with second decimal place 0. For example, if the number I want to count is 70.80,…
-
Offset Networkdays Count
I am currently using the following formula =IF([End Dt]@row = "", "", NETWORKDAYS([Start Dt]@row, [End Dt]@row, holidays:holidays)) If I have a Start Dt of 7/18/21 and an End Dt of 7/18/21 the formula give me an answer of 1. I want to be able to off set that by -1 because the task ended on the same day that it began,…
-
Interlinked individual Sheets to Master sheets
Hi Experts, I need help. I have 6 individual sheets that I need to rows to combine inside one Master sheet and update/add rows automatically when the individual sheets gets updates. Is this possible? I know it is possible when we use report builder but I want my Master sheet into Sheet because I am going to make a Metric…